SQL behind the PDM "Search.exe" tool

[jcapriotti]

Does anyone know the command line switch (argument) to output the T-SQL code behind the Search.exe for a search card? I had it years ago and can't find it.

[AlexB]

This is all I can find at the moment.

image.png (10.83 KiB) Viewed 2360 times

[jcapriotti]

This is all I can find at the moment.
image.png

Thanks.....those are documented, we use a few. This one is hidden and I can't find it.

[bnemec]

I'm curious, what'cha doing?

Is this the tsql we use in the report generator tool?

@Diaval might know this command line switch or the KB solution that has it. I tried looking it up there but no luck. Although I did learn how to edit my shortcut to search.exe to start with my "default" search card to save the three clicks getting to it in the tree.

[jcapriotti]

I'm curious, what'cha doing?

Is this the tsql we use in the report generator tool?

Searches for files just by "State" using the default "Complete Search" card take 25-30 seconds (This seems way too long). Whereas searching on a variable takes 2-3 seconds. Doesn't matter if its 1 file found or 100.

We have 830,000 entries in the Documents table. Tried this in our current version (2020sp5) and also in 2022sp5.....no difference.

Troubleshooting long ago, like over 10 years, I had the command line switch...or maybe it was a registry entry. What is did was save a text file after every search that contained the T-SQL that the search card was using behind the scenes. This was useful to figure out what the search was actually doing.

[JSculley]

SQL Server Management Studio provides query tracing and can show you the execution plan for the query. Make sure you enable tracing of RPC Starting events to capture the correct SQL query.

Pasting these queries into Management Studio, a search by state (on my server) is about the same speed as a search for a particular variable value ( 30-40 secs) with each result sets of 48000 and 53000 rows respectively with 226,700 rows in the Documents table.

[Diaval]

I think there is a registry setting that can be used to spit out the query used from the search tool but I don't have details about it. You could ask your VAR and see if they are able to get it from SW support.

[jcapriotti]

I think there is a registry setting that can be used to spit out the query used from the search tool but I don't have details about it. You could ask your VAR and see if they are able to get it from SW support.

VAR didn't know. However, Jeremiah Davis did respond on my Linkedin post that it is a registry key.

Under HKLM, down to PDMWorksEnterprise, create a DWORD DebugSQL with a value of 1. This creates a Search.txt at the root of the C drive. But with current security settings that I typically see, you have to run Explorer as administrator (search for Explorer, right-click, run as administrator) in order for this to create the text file.

I had to go directly to the "SEARCH.EXE" in the PDM and "run as admin" to get it to work. One of my add-ins throws an error when loading the search card but it does create the text file of the SQL query when run. I believe this setting goes back to WinXP so all of the extra security on Win10 must be causing the add-in errors.

[AlexB]

Wow, it's pretty awesome that you were able to track this down. This will definitely be going in my "obscure things that might help" file