For SQL Server 2008/2008 R2 installed on Win 7/Windows Server 2008, if we attempt to open .sql files with SSMS closed, the file does not open up in Management studio. The SSMS windows will just open (as below) but the file will not be displayed.
If the SSMS interface is already opened then it would open the file. Strange! Huh?
Note: This issue does not happen with SQL Server 2005.
To understand the issue better, we need to understand how file association take place. Whenever we launch a file Windows uses the Registry to discover which application handles it. This association is stored under the hive HKEY_CLASSES_ROOT. The value stored with this key is the File Class. For example, the extension ".txt" may have TextFile as its File Class.
A quick and easy way to find the file association is to run the command ‘assoc’ in the command prompt which will tell you the mapping of which folder under the registry hive HKEY_CLASSES_ROOT will be used.
C:\Program Files\Debugging Tools for Windows (x64)>assoc .sql
We collected a Procmon trace (http://technet.microsoft.com/en-us/sysinternals/bb896645) on the machine. On analysis found that when we open the .sql file the command line is called:-
Command line: "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe" /dde
Ideally we should also see the filename passed to the command line as well. We checked the command usage of ssms.exe. This tells us that there is a filename parameter in the command line but no parameter is passed.
ssms.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P password] [-E] [file_name[, file_name]] [/?]
[-S The name of the SQL Server instance to which to connect] [-d The name of the SQL Server database to which to connect] [-E] Use Windows Authentication to login to SQL Server [-U The name of the SQL Server login with which to connect] [-P The password associated with the login] [file_name[, file_name]] names of files to load [-nosplash] Supress splash screen [/?] Displays this usage information
Now you may say, yeah! yeah!.. It’s nice to know why it is not working. Now tell me what do to?
Under the registry hive HKEY_CLASSES_ROOT\sqlwb.sql.9.0\Shell\Open\Command you should see something like this
"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe" /dde
Change it to
"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe" "%1" /dde
%1- this is actually is used as a placeholder for the filename parameter.
After this change we verified that we are able to open the .sql file successfully. We verified using Procmon and now we could see the below command being called:-
Command line: "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ssms.exe" "D:\ Sample Query.sql" /dde
Hope this helps!
Written By: Devashish Salgaonkar,Support Engineer, SQL Support Team
Reviewed By: Balmukund Lakhani (Technical Lead, SQL Support Team) Akbar Farishta (Technical Lead, SQL Support Team)
Thanks for this post. Faced this issue earlier, glad I was not alone ;)
Thanks Sudeepta. Hope this would help others as well.
Good to know.
Similar post about SQL Profiler .trc files here: blogs.msdn.com/.../sql-profiler-doubleclick-does-not-open-trc-files-any-more.aspx
Keep such things flowing. Even I faced this issue. Great to see such tips.
I had given up on this subject - however, I learned this new trick today.
How can microsoft remove such feature that already exist. And why do we have to manually play around with registry to get back the feature.
Thanks for the post! This worked.
When I try to open .sql files on double click after opening SSMS with “ran as different user”, it gives “The operation could not be completed. Unspecified Error”.
Do I need to do something to open .sql files on double click when ran as different user?
I had a similar problem with SSMS 2012. But the problem after trying the suggestion here is that everytime I double-click a .sql file in Windows Explorer a new instance of SSMS 2012 opens?!?!?! I want the file to open in a new tab of an existing SSMS 2012 session. Is there a solution to this problem?