[9.2.349 Pro] - Custom Menu Items

[9.2.349 Pro] - Custom Menu Items

I've set up a custom menu item to pass on execution plan information to SQL Sentry Plan Explorer. The setup is the following:

	Quote:
	Menu Caption: Open in Sentry Plan Explorer Command Type: Copy output data to clipboard and run external program Where: Execution plan panes Data Format: XML Output File: Program to Run: C:\Program Files\SQL Sentry\SQL Sentry Plan Explorer\SQL Sentry Plan Explorer.exe

But it does not seem to work, it throws an exception. I tried to report it but when I clicked Submit Error Report the dialog said:

	Quote:
	Error Report Rejected: The error you are trying to report indicates that you are trying to access some database or object for which you do not have the required permissions. This security error has nothing to do with SQL Assistant.

which is a bit strange, the necessary object were already accessed since the execution plan was shown in SE and the action that failed should only have copied the execution plan xml to clipboard and start SQL Sentry Plan Explorer.

Likewise saving to an XML file doesn't work.

If I save it to xml and try and open it with SQL Plan Explorer it doesn't do anything, but if I right click the plan and do "Show XML" and paste that into SQL Plan Explorer it works.

The text in the saved XML vs "Show XML" in SQL Editor are very different, to top it off.

So "Show XML" then "Open in SE" with the options set to save it to an output file saves a better formed XML file that can be opened by plan explorer via CLI, but invocation from SQL Editor does nothing.

So, I think I may see why gemisigo is having issues:

https://i.imgur.com/KqSzROi.png

This is pretty normal, hit OK.

Hit OK to exit SQL Assistant settings.

Go back in to custom menu settings:

https://i.imgur.com/9KSMA9m.png

Suddenly the external program is empty, but Output file contains the program to run.

https://i.imgur.com/tRiSvZO.png

This, however odd it is, works:

https://i.imgur.com/39OO7LW.png

Even though you are copying to the clipboard and then running the program, it still makes the output file and successfully opens it! Without the output file in place, the preferences move the program to execute to the output file which throws an exception.

Also if you don't wrap the program to execute in quotes SQL Editor will tell you "C:\Program" does not exist.

I think "Program to Run" isn't a good name for that option. It's better be called "Command Line" as it may include command line parameters. You're responsible for the parameters and correct syntax. If the path or parameters include spaces, they should be enclosed in double quotes.

	SysOp wrote:
	I think "Program to Run" isn't a good name for that option. It's better be called "Command Line" as it may include command line parameters. You're responsible for the parameters and correct syntax. If the path or parameters include spaces, they should be enclosed in double quotes.

There are still bugs/idiosyncrasies with it though... the examples given above make it difficult to use because things save to the wrong spot or don't necessarily work.

For example. If I set it to save to output file and run program

and set my program to run to "C:\Program Files\SQL Sentry\SQL Sentry Plan Explorer\SQL Sentry Plan Explorer.exe" C:\temp\outputfile.xml

It doesn't launch the program, period.

The only way I got it to work is to "trick" it by telling it to copy contents to the clipboard, then I still had to set an output file (to avoid the program path being moved to the output file). Then to top THAT off, I did NOT have to specify the target file for SQL Sentry Plan Explorer to open in the "Command to run" like my above example, I simply told it what program to run with no parameters and SQL Editor must have told it to open my C:\temp\outputfile.xml itself.

Thank you. I will certainly pass your input to the team and ask them to fix whatever is necessary to fix to make it work well.

I did find this in the SQLAssistant.log that is likely related to gemisigo's post:

Line 26253: exception message : Cannot create file "C:\Program Files\SQL Sentry\SQL Sentry Plan Explorer\SQL Sentry Plan Explorer.exe". Access is denied.

This happens when you leave output file blank, but set the program to run. When you save it and exit the SQL Assistant settings dialog, it seems to save the program to run as the output file (like seen in one of my posts above)... so when gemisigo tries to run his custom menu item it's trying to output the plan and overwrite the targeted executable.

Yes, this must be how it happened. At first I wasn't sure what you were talking about because simply applying changes puts everything where they belong. I almost never close SA Options, I just apply changes and let that window rest in the background since I use it pretty frequently. But now I repeated the steps as described (using OK instead of Apply) and it had the contents of External Program moved to Output File.

You might also take a look at that xml thing. Having the format set to XML still throws exception. I had to set it to Default so that SA could open SSPE but then the saved xml was not a valid execution plan desrciption, thus SSPE failed to load it. When I set the pane to show XML, I've seen this:

and the saved file content was:

I'm sorry for the images but it seems that putting xml into a reply (regardless of being in code/quote) will remove much of the contents when displaying the post.

Yes you have to set the format to default rather than XML

Also I told my menu to pop up on all menues because to get the right XML you need to right click show xml on the query plan and then open it with SPE (right click menu) otherwise you get a malformed xml plan.

This issue is supposed to be fixed in the new maintenance version 9.5

I'm having issues with this with 11.1.107, I can't get it to launch SQL Plan Explorer at all.

I have it set to Copy Data to Clipboard and execute program. The clipboard never receives the contents, either nearest I can tell.

Code:

2/5/2019 11:41:21 AM Ssms.exe Error: Exception: Unhandled
operating system : Windows 10 x64 build 18363
system language : English
system up time : 3 days 3 hours
program up time : 20 hours 54 minutes
processors : 12x AMD Ryzen 5 3600 6-Core Processor
physical memory : 8754/16315 MB (free/total)
free disk space : (C:) 163.13 GB
display mode : 1920x1080, 32 bit
process id : $8d60
allocated memory : 869.41 MB
largest free block : 1.97 GB
executable : Ssms.exe
current module : SqlAssistPrefs.dll
module date/time : 2019-11-27 13:21
version : 11.1.107.0
callstack crc : $9a4e545e, $63f60f12, $21950fd2
exception number : 18
exception class : EAccessViolation
exception message : Access violation at address 2B85D23E in module 'SqlAssistPrefs.dll'. Read of address 00000008.

cpu registers:
eax = 00000000
ebx = 00000000
ecx = 00000000
edx = 00000000
esi = 00000000
edi = 00000000
eip = 2b85d23e
esp = 0113dfe8
ebp = 00000000

disassembling:
[...]
2c57789f mov [ebp-$15], al
2c5778a2 2149 cmp byte ptr [ebp-$15], 0
2c5778a6 jz loc_2c577a1e
2c5778ac 2151 mov eax, [ebx+$28]
2c5778af xor edx, edx
2c5778b1 > call -$c81396 ($2b8f6520) ; Vcl.Controls.TWinControl.GetControl
2c5778b6 mov edx, [$2c567140]
2c5778bc call -$e3d2fd ($2b73a5c4) ; System.@AsClass
2c5778c1 mov [ebp-8], eax
2c5778c4 2153 mov eax, [ebp-8]
2c5778c7 movzx eax, byte ptr [eax+$8f4]
[...]

main thread ($7040):
2b85d23e +006 SqlAssistPrefs.dll System.Classes TList.Get
2c5778b1 +049 SqlAssistPrefs.dll ExecPlanUnit 2151 +4 TExplainPlanPan.SaveData
2c55ffa0 +128 SqlAssistPrefs.dll FloatHostForm 477 +20 TDockableDataPan.CustomCommandClick

thread $b288:
2c580b6e +42 SqlAssistPrefs.dll SyntaxUnit 1887 +4 TSyntaxThread.Execute

thread $b654:
2bc14f5e +26 SqlAssistPrefs.dll VirtualTrees 6375 +4 TWorkerThread.Execute

thread $a28c:
2c580b6e +42 SqlAssistPrefs.dll SyntaxUnit 1887 +4 TSyntaxThread.Execute

Even just trying to dump the plan to XML (to file) gives me an Unhandled Exception.

This issue has been confirmed as a defect and we are developing a fix for that. We are treating it as a high priority issue, so I expect a fix to be released soon.