通过

Hey Mr. SSMS! Why I am unable to open .sql files using by double click

  • 项目

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.

clip_image002

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.

Approach

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.

For eg.

C:\Program Files\Debugging Tools for Windows (x64)>assoc .sql

.sql=sqlwb.sql.9.0

We collected a Procmon trace (https://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.

Usage:

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

Resolution

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)

Comments

  • Anonymous
    January 10, 2012
    Thanks for this post. Faced this issue earlier, glad I was not alone ;)

  • Anonymous
    January 10, 2012
    Thanks Sudeepta. Hope this would help others as well.

  • Anonymous
    January 10, 2012
    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

  • Anonymous
    January 11, 2012
    Thanks Jason.

  • Anonymous
    January 18, 2012
    Hey Balmukund, Keep such things flowing. Even I faced this issue. Great to see such tips.

  • Anonymous
    January 23, 2012
    I had given up on this subject - however, I learned this new trick today.

  • Anonymous
    June 27, 2012
    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.

  • Anonymous
    February 17, 2013
    Thanks for the post! This worked. But, 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?

  • Anonymous
    July 07, 2014
    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?