TIP: Open SQL Files in a Visual Studio Project Into the Same Instance of SSMS

Considering how integrated Microsoft tools usually are the result is frustrating when you tell Visual Studio to open SQL files using Sql Server Management Studio (SSMS). I really don’t like using Visual Studio to edit T-SQL files but in the past, before I discovered this tip, each SQL file I opened would open in a new instance of SSMS. Try it:

1. Open a solution which contains SQL files
2. Right-click any SQL file and select “Open With…”
3. Click “Add”
4. Browse to "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" or if you’re running x64 Windows "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe", then click “OK”
5. Click “Set as Default” and then “OK”

Now open multiple SQL files. Each time you’ll get a different instance of SSMS opened. What a pain!

NOTE: This entire article applies to SQL 2005, just replace SSMS with SQLWB.

How do you resolve this? Repeat steps 1-3 above, but at step #4 enter the following values:

• Program Name: “explorer.exe”
• Friendly Name: “Windows Explorer”

Repeat step #5 (set as default) above and then click OK. Now, open additional files. They should all open in the same instance of SSMS.

It would seem that Visual Studio issues a command to SSMS.exe which includes the path of the file selected in the solution explorer. It is up to SSMS to check for a new instance, which it doesn’t. But when you pass the file name to explorer it gets opened up in the same instance.

QUIRK WARNING!

If SSMS is not already open, the first file you attempt to open (not first time ever, but every time you open an SQL file from Visual Studio and SSMS isn’t open yet) SSMS will open, but your file will not. Click the file a 2nd time and it will open the file this time. Don’t ask me to explain it it just is (and I have no idea why).

Conclusion

The result when you tell Visual Studio that SSMS is the default editor makes sense, but I don’t get why it would be different when you tell explorer to open it. Maybe if I were a Windows developer instead of a web developer I would know the answer. But either way, now you know. Enjoy.

Viewing XML Query Plans in SSMS

Well after a long absence due to a crazy workload, I'm baaaack!

Keepin' it simple at this point. I've resumed studying for MCTS exams and I've been reading about dynamic management views. I love them. What's more, I love the detail they provide and the capability for deeper analysis.

So today I was exploring sys.dm_exec_query_stats and I run across the ability to use CROSS APPLY to view the execution plan xml or the sql text using sys.dm_exec_query_plan and sys.dm_exec_sql_text. I'm really excited about this - I figure I can use all these together like I might use Profiler to find problem queries. For example:

SELECT * FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
ORDER BY max_elapsed_time DESC

The result is an additional column, query_plan. Which is the XML show plan. But when I tried to save the result and open it to view the graphical execution plan I got errors.

The first error was an "unexpected error" which can be reproduced by first, clicking the link in the results pane which is available when
a field contains XML. This opens a new query window to display the XML. When I used "Save As..." to save the file as a *.sqlplan file, then tried
to open the file I get an "unexpected error" message from Visual Studio (SSMS). Fortunately, I found that if I close the file and then open
it to view the graphical plan, it works. I'm assuming there must be some file handle problem.

The second error occurs if I right-click the xml in the results pane and select "Save Results As...". I'm sure some of you who will be
reading this have already realized what I did wrong. Yes, it's embarrassing, but I write this blog to publish those stupid mistakes in hopes
that someone else experiencing a brain-lapse (as I often do) might find help.

Anyway, back to the problem, when I try to open the results I get the following:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Error loading execution plan XML file {path}\query_plan4.sqlplan. (SQLEditors)

------------------------------
ADDITIONAL INFORMATION:

There is an error in XML document (1, 1). (System.Xml)

------------------------------

Data at the root level is invalid. Line 1, position 1. (System.Xml)

------------------------------
BUTTONS:

OK
------------------------------

For those who haven't figured the problem out already, the answer is that you can't open a CSV file (output of "Save Results As...") using the execution plan editor, no matter what extension you save it as. Just cause the extension is .sqlplan, doesn't mean that's the contents of the file.