SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[9.2.349 Pro] - Custom Menu Items
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[9.2.349 Pro] - Custom Menu Items
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post [9.2.349 Pro] - Custom Menu Items Reply with quote
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.
Tue Aug 29, 2017 6:19 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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.
Tue Aug 29, 2017 9:42 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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.
Tue Aug 29, 2017 10:24 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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
Tue Aug 29, 2017 10:30 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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.
Tue Aug 29, 2017 10:38 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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.
Tue Aug 29, 2017 11:18 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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.
Tue Aug 29, 2017 11:25 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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.
Tue Aug 29, 2017 11:42 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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.
Tue Aug 29, 2017 11:59 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
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.
Tue Aug 29, 2017 1:14 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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.
Tue Aug 29, 2017 1:29 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
This issue is supposed to be fixed in the new maintenance version 9.5
Mon Dec 18, 2017 3:24 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
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

Thu Dec 05, 2019 1:34 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 804
Country: United States

Post Reply with quote
Even just trying to dump the plan to XML (to file) gives me an Unhandled Exception.
Thu Dec 05, 2019 2:23 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
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.
Fri Dec 06, 2019 9:50 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.