Tuesday, 17 December 2013

Opening Files Your Way

Introduction

I was having problems with opening scripts. At first, I couldn't associate the .sql file with SSMS at all. Once I was able to open the file in SSMS, I found that each script opened in a new window, and I wanted them to open in the same window.

So I played around a bit, and here's what I found to fix it. None of this is official, just what I found to work and my theory behind it.

Investigation

So when I open a file of any type, my registry is checked to see what command to use to open that file. The keys are all stored in the HKCR (HKEY_CLASSES_ROOT) hive. If I was to open up the registry editor  and expand that hive, I would find all the file associations I have. In my case here, I opened ".sql" to find that the (Default) value was set to "sql_auto_file". Other people had other values, but it doesn't matter. It turns out that this value, when set, corresponds to another key within the HKCR hive that provides information on how to open the file.

So I opened that key up to find only a sub-key called "shell" with its own sub-key called "open". Within "open", there was only an empty (Default) value. That's why it wasn't working. It wasn't associated with anything. I examined some of the other keys in the hive and copied the approach they took. I set the (Default) value to be " "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe" "%1" ", according to the SSMS command-line arguments. Now, when I double-clicked my .sql file, it opened my file in SSMS. Yay!

But when I had an instance of SSMS already open, it would open any additional files in new windows. Not good enough.

So I started doing some research. People were talking about the "/dde" switch. I added it, but it didn't change anything. Files were still opening in new windows.

So I started researching what some other people were posting as the keys to message boards. Some of them had a key under "Open" called "ddeexec". I replicated the keys they were posting, creating the same keys on my end. It meant creating a sub-key under "Open" called "ddeexec", with two sub-keys called "Application" and "Topic". The (Default) key for "Application" was set to "ssms.11.0" and the one for "Topic" was set to "system".

Now, when I used the "/dde" switch with these new keys, the scripts opened in an already open window if there was one available! As it turns out, the window must already be open or it will create it. If I wanted to open several files at once, I had to make sure a window was already open. Makes sense. If it runs these commands at the same time, each one won't open in time to tell the next file there's already one open. I'm not sure if there's a fix for this.

I figure that this means that the "/dde" switch asks program to look at the "ddeexec" key and follow instructions there...

I noticed that under some of the keys, there was an additional key beside "Open" called "edit". Sometimes, when I right-click a file, I can see "edit" as a context-menu option. In the registry, this key had the same structure as "open". It turns out that you can specify a different program for editing than opening. Neat! So I decided to add my own key beside "Open", called "Edit In New Window". I created the sub-key "Command", and used the value from my previous values, but without the "/dde" switch. Now, when I right-clicked my .sql file, there was a new command: "Edit In New Window"! When I clicked it, it opened in a new window. I realized that I could add several different associations, each named as I wished. So cool :)

Key Structure

Here's the structure of my key:

  • HKCR\.sql
    • (Default) value = Key to look at when opening file
  • HKCR\<value from above>
    • \Shell
      • \Open
        • \Command
          • (Default) value = command to open file (%1 represents file path)
        • \ddeexec
          • Application
            • (Default) value = version of SSMS
          • Topic
            • (Default) value = "system"
      • \Edit
        • Same structure as "Open"
      • \<your custom name here>
        • Same structure as "Open"
There you go. Use that to repair how SSMS opens your files, and customize your context-menu entries :)

Possibilities

This can be used several ways. Some possibilities:

  • Open SQL scripts connected to a specific SQL server instance
  • Create short-cuts for opening files in multiple programs
  • Link to scripts to run multiple commands for a single file
  • Use complex move or copy commands with a single click

5 comments:

  1. Great and useful article. Creating content regularly is very tough. Your points are motivated me to move on.


    SEO Company in Chennai

    ReplyDelete

  2. What an awesome post, I just read it from start to end. Learned something new after a long time.


    SAP FICO Training in Chennai

    SAP SD training in Chennai

    ReplyDelete
  3. Credit rating reporting is not something but oracle fusion procurement manner wherein viable avail with options to offer the customer or the monetary employer commercial enterprise organization with all of the credit rating and debt facts
    thank regards
    oracle fusion procurement online training
    oracle fusion procurement training

    ReplyDelete
  4. Thank you for this valuable information. Get your business to the next level in simple steps. erp in chennai | erp for automotive industry chennai

    ReplyDelete
  5. I have read your blog its very attractive and impressive. I like it your blog.


    SEO Services in India SEO Company in India SEO Company in India

    Guaranteed SEO services Guaranteed SEO

    ReplyDelete