To help find where a file has been moved to and by who, I have developed a Transact-SQL query and saved as a Conisio Report file (.crp) that can be safely run through the Report Generator feature of SOLIDWORKS PDM Professional. This article will provide the query to the public as-is, elaborate on the functionality of this query, provide how to use the query, and provide a few use case scenarios.
How do I install and run a .crp file query?
This blog explains the process of installing and using a report generator file.
Where can I get your Go_Search_For_Moved_Files.crp file?
You can download the latest version of this .crp file here.
What if I can’t find my file when it has not moved?
This document is the third part in a series of articles for locating lost files. You can try locating the file using our first query which tackles many common situations that lead to a lost file. The first article in this series can be accessed here.
Can I use the report generator in SOLIDWORKS PDM Standard?
Note that the report generator feature is not included in SOLIDWORKS PDM STANDARD. However, we are happy to provide our customers the “raw” query to run against the PDM STANDARD Database in SQL Management Studio so your DBA can run the query for you. It is recommended to take a full backup of PDM prior to running any query. The query can be accessed here.
The Go_Search_For_Moved_Files query works like a PDM search. However, it is not bound the visibility restrictions of set permissions and can serve as an overview of all files ever moved. The query is also free from the limitations of the PDM search cards which, by design, do not show deleted files in the results. It does not return files themselves like a PDM search card does, it is just a table of metadata from the vault database. It is a very inexpensive query, meaning that it will not use much database server CPU to complete a large search. The query is particularly useful to administrators who have a specific reason to know where files have been moved to. See use case scenarios below.
The search interface of the query is simply an input text box where you can search for a specific file by providing its precise file name with extension, or you can use percent signs “%” as wildcards in place of variant text.
In the screenshot above, %.sld% is used as search criteria. This text means that you want to output information about all files in the entire vault that has the text .sld in the first three characters of the extension and have been moved. The output results would include but not be limited to .sldasm, .sldprt, and .slddrw files. If you are interested in only Microsoft Excel files, for instance, you could search for %.xlsx or %.xls% if you also need Excel files with macros (.xlsm).
Clicking OK on the search interface of the query will output the flowing information:
Query search criteria used in the example below: “%.%” without quotes.
Note: the file locations are relative to the root folder of the vault.
Path of “\” means C:\MyVaultname\. It is also worth mentioning that if you are interested in seeing the chronological order that the file was moved, the best place to view that is in the history of the file. In this query, to make sense of the chronological order by which the file was moved, follow this path:
For quick reference, here are the Tips/Hints provided in the description of the query. To view them in the vault, once the query is imported, you can right-mouse-click the name of the query and select properties.
* The Document ID is returned to help distinguish same-name files.
* "%.%" without quotes returns all moved files.
* Time is in format (YYYY-MM-DD 24Hr) and relative to the database local time.
* These results can be exported! Instructions: Installing and Using a Report Generator File.
About Francisco Guzman
Francisco Guzman is the PDM Technical Support Lead at GoEngineer, and is pursuing his degree in mechanical engineering at the University of Utah. In addition to providing guidance and support to SOLIDWORKS and SOLIDWORKS PDM customers, Francisco also provides support for DriveWorks design automation. He won the world-wide DriveWorks reseller CPD contest as the best DriveWorks AE for 2015. For fun, he designs, 3D-Prints, builds and races custom first-person-view (FPV) racing drone frames.
Get the latest articles delivered daily to your inbox, unsubscribe at any time.