33

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

George_21
  • 331
  • 1
  • 3
  • 3
  • When you say *instance* you really mean *connection*, right? You want each SQL script to share a single connection? – Yuck Aug 19 '11 at 14:51
  • 8
    @Yuck, no he means when he goes to Windows Explorer and double-clicks a .sql file, it opens a *new* instance of Management Studio. – Aaron Bertrand Aug 19 '11 at 14:53
  • Aaron, you're right. This is going to kill me soon!. Any clue?! – George_21 Aug 19 '11 at 15:05
  • If you're running Win7 the file associations you are looking for are in "Control Panel\Programs\Default Programs" – Brian Rudolph Aug 19 '11 at 15:13
  • That is true @Brian, but all you can do there is set the program... you can't specify any arguments or change the behavior in any way. I think this will require further registry work, I just don't know what key(s) to touch. – Aaron Bertrand Aug 19 '11 at 15:17
  • 1
    possible duplicate of [SQL Management Studio won't open scripts on double-click](http://stackoverflow.com/questions/1726577/sql-management-studio-wont-open-scripts-on-double-click) – neves Jul 23 '14 at 15:29
  • Note to readers: this question relates to opening SQL files FROM VISUAL STUDIO, not from windows explorer!! This confused me for some time and explains all the talk of 'open with explorer.exe' which is otherwise just weird. – Rory Sep 11 '14 at 13:31

12 Answers12

23

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.

Hannah Vernon
  • 3,367
  • 1
  • 26
  • 48
G21
  • 1,297
  • 2
  • 18
  • 39
  • I guess I did something wrong or this solution is not always working: when I set C:\Windows\explorer.exe to the default program for .sql files and then click on a sql file, it keeps starting new explorer.exe processes until Doomsday. – Josien Sep 21 '12 at 12:07
  • And somehow, the .sql extension was chucked out of the registry as well :-) – Josien Sep 21 '12 at 12:25
  • I've tried this solution, but it almost froze my machine. Windows explorer entered in a kind of look trying to open the file, and I had to hit Ctrl-Alt-Del to close it. – neves Jul 23 '14 at 15:25
  • 3
    Note to readers: this question relates to opening SQL files FROM VISUAL STUDIO, not from windows explorer!! This confused me for some time and explains all the talk of 'open with explorer.exe' which is otherwise just weird. – Rory Sep 11 '14 at 13:32
  • Side-note: explorer approach mentioned in the wnaser works perfectly with Visual Studio 2012/SSMS 2012 – G21 Jun 05 '15 at 12:49
  • I made SSMS v14.9 (2019) default for .sql files. Without SSMS running I ran this from powershell: DIR *.sql|%{EXPLORER $_}. It started SSMS and opened all the .sql files in the directory in the same instance. So the quirk seems to be gone in newer versions. – bielawski Aug 04 '19 at 00:45
11

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.

Xavier
  • 389
  • 7
  • 20
  • It works, for some reason I tend to run development-related software under admin privileges, so I'm not happy with this setting. Nevertheless, if it works, one doesn't argue with it. – Kamil Gosciminski Apr 09 '19 at 09:06
  • A quick note for users who want to have SSMS always open with Administrator privileges. Find SSMS in your start menu. Right-click>More>Open File Location. Once you see the shortcut to `Microsoft SQL Server Management Studio *` in Windows Explorer, Right-click>Open File Location. Then Right-click on `Ssms.exe`>Troubleshoot compatibility. Then click `Troubleshoot program`. Put a tick by `The program requires additional permissions` then hit Next. Now it forces you to press `Test the program...`. Then press `Next` and then `Yes, save these settings for this program`. – Arvo Bowen Jan 30 '20 at 15:27
  • Sadly, for some reason though, this did not work for me. Using Microsoft SQL Server Management Studio 18. :( – Arvo Bowen Jan 30 '20 at 15:28
  • For SQL Server Management Studio 2019, there is no "Privilege level" under Compatibility. Right Click SSMS > Properties > Advanced > Untick Run as administrator. – Ankita Jun 17 '22 at 20:12
3

Similar issues have been reported multiple times, but they aren't addressing the issue (removed links because Microsoft erased Connect without much thought):

Did you install Visual Studio (or any Visual Studio components) after installing SQL Server?

My guess is that the Connect items will not be fixed - SQL will blame Visual Studio, Visual Studio will blame SQL, and the items will remain open...

So here is what I suggest: run a repair of SQL Server from Programs and Features and then re-apply the latest service pack of SQL Server 2008 (SP2).

Also the "File Types" interface was moved from the Tools / Folder Options interface to the Control Panel. Go into Control Panel > Default Programs > "Associate a file type or protocol with a program." However it doesn't seem to have the options here to add command-line options like "%1" or /dde.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Also this TechNet thread has several recommendations for replacements of the File Types tab and bringing back a lot of those options that seem to have been moved to individual Open With wizards or taken away completely. Perhaps you could try one or more of these utilities and see if they have the ability to restore proper functionality: http://social.technet.microsoft.com/Forums/en/itprovistadesktopui/thread/0ee43527-8d6e-4da3-80dc-80058215d7e6 – Aaron Bertrand Aug 19 '11 at 15:24
  • 1
    The first 3 Links for this answer do not work anymore. – John Foll Feb 04 '21 at 21:00
  • @JohnFoll Yeah, that's what happens after 10 years. – Aaron Bertrand Feb 04 '21 at 22:25
1

Go to Tools >> Folder Options >> File Types and apply below settings. It worked for me finally!!! (after half a day search on google).

  1. Click New/Edit (on this window "Confirm open after download" should be check and "Always show extension" should be unchecked)

  2. In next window, make below entries

       - Action: Open
   - Application used to perform action : Your ssms.exe path like "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
   - Check "Use DDE" check box
   - DDE Message : Open("%1")
   - Applicajtion sqlwb.9.0
   - DDE Application Not Running: Keep it empty
   - Topic : system

    Regards, Sandeep Gaadhe

MachineLearning Enthusiast
  • 11
  • 1
1

I am using SQL Server Management Studio 2012 Express and I have made this to work by pointing "Program" to "explorer.exe"

This is how my register is set up: "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe" /dde

at

HKEY_CLASSES_ROOT\ssms.sql.11.0\Shell\Open\Command

Marco Alves
  • 2,776
  • 3
  • 23
  • 33
1

Try adding these reg keys as well, but make sure you put the /dde back in:

[HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\ddeexec]
@="Open(\"%1\")"

For the record, here is my entire export for that section:

[HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open]

[HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\Command]
@="\"C:\\Program Files (x86)\\Microsoft SQL Server\\100\\Tools\\Binn\\VSShell\\Common7\\IDE\\ssms.exe\" /dde"

[HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\ddeexec]
@="Open(\"%1\")"

[HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\ddeexec\application]
@="sqlwb.9.0"

[HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\ddeexec\topic]
@="system"
Mat
  • 202,337
  • 40
  • 393
  • 406
Brian Rudolph
  • 6,142
  • 2
  • 23
  • 19
1

My issue with multiple SSMS instances version 19 was fixed by changing HKEY_CLASSES_ROOT.sql Default key to

enter image description here

Serge V.
  • 3,377
  • 3
  • 20
  • 28
  • most visually easy option to cover. Please note that now days its SQL v19 available so just replace to `ssms.sql.19.0` – vibs2006 Mar 01 '23 at 13:49
0

I've 2005 & 2008 SSMS installed so I defaulted to the 2005 instance. If I have 2008 ssms open when opening a .sql file from windows explorer it will now open in the existing 2008 ssms (and not try to open a new instance!)

coldsurfer
  • 1
0

From Windows Explorer, one can drag-and-drop selected files (single or multiple) into an SSMS window.

ko la
  • 431
  • 4
  • 9
0

One thing to check is if you have multiple versions of SSMS. If you happen to be working in an older version, double clicking on a file will open the new SSM version. Or whatever the default version is for sql files.

Andy
  • 21
  • 3
0

A quick note for users who want to have SSMS always open with Administrator privileges.

  • Find SSMS in your start menu. Right-click>More>Open File Location.
  • Once you see the shortcut to Microsoft SQL Server Management Studio * in Windows Explorer, Right-click>Open File Location.
  • Then Right-click on Ssms.exe>Troubleshoot compatibility.
  • Then click Troubleshoot program.
  • Put a tick by The program requires additional permissions then hit Next.
  • Now it forces you to press Test the program....
  • Then press Next and then Yes, save these settings for this program.

Arvo Bowen Jan 30 '20 at 15:27

This work perfectly.

cachique
  • 1,150
  • 1
  • 12
  • 16
Sunil Bagade
  • 1
0

I know this is quite an old post and there are loads of different answers with possible solutions. But here's one I think is new (at least to SO).

I suddenly starting getting this behaviour (i.e. each new file would open in a new session) and couldn't figure out why. Turns out it was because in my SSMS session I had an open dialogue which had dropped into the background.

A soon as I closed the dialogue, new files started opening in my original session /facepalm

Maybe everyone knows this already but just in case it saves anyone 20 mins!

RT72
  • 181
  • 1
  • 11