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

  • Article

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)