SQL Query for document Context/Location

Question

Does anyone have a SQL query to list of all documents, with their context & location.

I did setup a query in report builder, however I run into a record count limitation (65k) when exporting. When using the SQL query from this report in SQL itself, it only brings over the IDs for context and folder path (I need the names). I must need to link to another table, even though the report builder SQL is not showing such?

BineshKumar1 · Accepted Answer

I have attached a query which I used for our prod a couple of time. Hope it helps.ThanksBinesh KumarBarry Wehmiller

BenPerry · Answer

Joe‌,What you might be experiencing is that Query Builder handles the different types of contexts that your document could reside - Project, Product, Library.  But with raw SQL to bypass the query limit, you'll likely need to create 3 separate SQL queries to search against those 3 different types of contexts.Do you have a test site?  Perhaps you could set the query limits to -1 on that site, and execute the query.  Then you can find the number of results (say perhaps 95k) and then temporarily set your production query limits accordingly (say 100k).  Does that help?...or... perhaps what you're talking about is not a "query limit" limit, but instead an export functionality limit.  Perhaps CS52471 describes your situation.  I have also experienced it.  Therefore I used the information in that article to set the Windchill property to 1,048,576 (or a few less than that) - the new row limit in the newer versions of Excel.

Sign up

Please use your PTC eSupport account.

Welcome to the PTC Community

Please use your PTC eSupport account.

Scanning file for viruses.

This file cannot be downloaded