How to open multiple .sql files in only one ssms instance

Question

I'm DBA with a new server box running Windows Server 2008 Enterprise SP1 with SQL Server 2008 and SQL Server Management Studio.

In my previous box when working in Visual Studio with database solution with lot of .sql files I used to double-click on the .sql files and they were opened in the same SSMS instance (if any already opened). However (even when having exactly the same installation in the new box) I'm now getting a new SSMS instance when double clicking in each sql file. Its really painful one .sql - one SSMS instance so I started my research about that.

What I've tried till now:

1) Right click on the .sql file > "Open With" option > "Add" option > "Program Name: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" and "Friendly Name: SQL" > "Ok" > "Set as Default". Does NOT work. Still having one sql - one ssms instance.

2) Perfomed Run > regedit > and modify the value data of HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\Command and replace /dde with "%1". DOES NOT WORK

3) Right click on the .sql file > "Open With" option > "Add" option > "Programe Name: explorer.exe" and "Friendly Name: Explorer" > "Ok" > "Set as default". Does NOT work. When double-clicking on the .sql files Notepad comes with scripts content.

4) Some pages like this

http://social.msdn.microsoft.com/Forums/en/sqltools/thread/ac2f39c5-66e5-495c-b7a6-47e743853baf

asks to change file association but when opening Windows Explorer > Tools > Folder Options ... I found there is no "File Type" tab. How annoying!

Can someone help me on that? My patient it's really running out.

Thanks in advance

Answer 1

After spending most of my working day looking for the solution I finally found it. Thanks God, I was almost running out with this issue. Hope it helps somebody else!!!.

Update: linked domain expired, so here's what it said:

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.

Answer 2

I have this problem before, I found there is a simple solution -> just check if your SSMS is under "Run this program as an administrator" option (right click on the shortcut icon -> property -> Compability -> Privilege level). If yes, untick the option and try double click the sql file.

Hope this helps.