Building a SQL Server Management Studio Add-in
Various people have built SSMS add-ins and some have even given tips on writing one, but very few of them explain in enough detail how to start from scratch. Joseph Cooney recently published an excellent post, but by the time I found his article, I'd already written most of this one, so here's a second view on how to do it.

I suggested last year that everyone should vote for extensibility support in the next version of SSMS but then began learning more about creating add-ins without Microsoft's support, and with a bit of help from Sean Price, I recently finished an add-in to display recent queries: Total SQL Recall.

I've included a template project (DemoAddin) at the end of the post, which should help getting started.

1. Creating an Extensibility Project

The first step is to create an Exstensibility Project using the Visual Studio wizard. Since this part of the process is the same for Visual Studio and SSMS add-ins there's good documentation available on MSDN and elsewhere, and even one article aimed at SSMS add-ins, so I'm not going to go into too much detail. The important choices are:

  1. New Project dialog: Select Extensibility (under "Other Project Types") then Visual Studio Add-in, and launch the wizard
  2. Wizard Page 1: Select your language
  3. Wizard Page 2: Select Microsoft Visual Studio 2005
  4. Wizard Page 3: Fill in name & description
  5. Wizard Page 4 & 5: Ignore these options for the moment
  6. Wizard Page 6: Create the template

To switch the project from a Visual Studio to an SSMS add-in, open the My Project / Debug page. The Start External Program option needs to be changed from Visual Studio to SSMS, which by default is found at:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe

 The /resetaddin switch is a handy feature if you're developing Visual Studio add-ins - it removes the commands installed by your add-in, effectively allowing the add-in a clean install next time you start up the IDE. Unfortunately, because SSMS doesn't support add-ins, this option is not available and we'll need to do this manually. Clear the command line options and we'll deal with this later.

Clear the working folder too, unless you need it set to something specific for your add-in.

2. References

There's extensive documentation on MSDN regarding Visual Studio's EnvDTE object model, which is at the heart of Visual Studio add-in development, and most of this applies to SSMS. Most UI elements are the same for both environments but if your add-in relates to anything SQL-specific then you're going to need references to SSMS assemblies and the documentation on these is non-existent.

As explained in the ASP Alliance article I mentioned earlier, there are two places to look for SSMS assemblies:

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\

So far I've found that all the assemblies I've needed are in the IDE folder, but it will depend on what you're doing with your add-in.

One thing which will definitely help if you're digging into the assemblies in search of obscure objects or methods is Lutz Roeder's Reflector, a fantastic little tool which lets you look inside assemblies and see what they have to offer.

3. IDTExtensibility2 Events

Once you've got the references sorted out, you're ready to start coding.

The template class that Visual Studio has created implements the IDTExtensibility2 interface, but only one of the methods has any code in it so far: OnConnection.

OnConnection "occurs whenever an add-in is loaded into Visual Studio" according to MSDN - in our case the event will fire whenever you start SSMS, once the add-in is installed.

OnConnection will probably be the most important method of the interface for your add-in, but the others can be useful if you need to save settings as the add-in is unloaded or something similar.

4. SSMS Events

Handling SSMS events is one of the trickier aspects of writing the add-in, the problem being that you don't know what events there are to handle. I suspect Reflector could help here, but the method I used was suggested by Sean.

To add a handler to an event we need to know the command to which that event belongs. The easiest way to find this is to loop through the commands and look for the name which sounds most like what you're after.

For Each com As Command In _DTE.Commands
   Debug.WriteLine(String.Format("Name={0} | GUID={1} | ID={2}", com.Name, com.Guid, com.ID))

The output from this will be quite large, but it's normally possible to identify a few commands as likely suspects. The next step is to get the CommandEvents object and see what events it has, then add handlers for the ones you're interested in. For example:

_CommandEvents = _DTE.Events.CommandEvents("{52692960-56BC-4989-B5D3-94C47A513E8D}", 1)

' add event handlers for the events
AddHandler _CommandEvents.BeforeExecute, AddressOf OnBeforeExecute
_CommandEvents.AfterExecute, AddressOf OnAfterExecute

The _CommandEvents object must be a global variable which does not go out of scope at the end of the OnConnection method. If the _CommandEvents object goes out of scope then the registration of the delegate is lost and the handler method will not be called.

5. Installing and Debugging

As I mentioned earlier, we need to manually reset the add-in every time we build it. The easiest way I've found to do this is to add a deployment project (vdproj) and include this in the debug configuration to build and install a new MSI every time you run the code.

When you add the primary output of your add-in project, the detected dependencies will include a bunch of SQL Server DLLs which you don't need. Exclude all of these apart from the framework - the dependencies are part of SQL Server, which will obviously be installed before your add-in.

Set the Register property of the primary output to vsdrpCOM.

This takes care of building the MSI file, but we don't really want to have to install it manually every time we build it. Installing the MSI manually also means you have to build the MSI, install it, then run the project to debug, which is a bit of a drag.

The installation of the MSI can be automated using a PostBuildEvent, which allows you to debug your add-in in exactly the same way you would a normal project, simply by hitting F5. The code is compiled, the MSI built and installed, then SqlWb fires up and you're debugging. The PostBuildEvent just needs to install the newly built MSI:

msiexec.exe /quiet /i "D:\Visual Studio 2005\Projects\SSMSAddin\Debug\SSMSAddin.msi"

So far I haven't been able to find a way of automatically uninstalling the add-in (the PreBuildEvent doesn't seem to work reliably), but it must be possible. I just right click on the deployment project and manually uninstall after every run of the code.

There's one more step required before we can actually run our add-in. The MSI takes care of registering the add-in classes, but doesn't let SQL Server know about it, so we need to add a registry entry in the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\AddIns\

Once again, the setup project can take care of this. Go to the registry section and create the entire path to the Addins key, then under the Addins key add another one telling SQL Server the namespace and class-name of the add-in. Within this final key, create a DWORD called LoadBehavior, with the value 1. You can also add a name and description:

Registry Settings

 If you're using SQL Server 2008 then the registry key is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\Tools\Shell\AddIns\

You should now be able to hit F5 and debug your add-in - you can put a breakpoint on OnConnection in the Connect class to test this. If debugging isn't working then check that the setup project is included in the debug configuration and is being built when you run the code - I had one experience where the configuration manager settings didn't seem to be working until I reset the Visual Studio settings.

If you get a message that the 'PostBuildEvent' failed with error code 'XXXX' then something is wrong with the MSI installation. There are various possibilities:

  • The MSI file has not been built because of a build error in the main project
  • The path to the MSI file is wrong
  • The add-in is already installed (uninstall is by right-clicking on the setup project and selecting uninstall)

If it's not immediately obvious what's wrong then run the PostBuildEvent in a command window, without the "/quiet" switch.

6. Add-in UI and Startup Mode

Page 4 of the Add-in Wizard asks a few questions about the UI and startup mode of the add-in.

Checking the first box (the UI question) causes the wizard to generate a Connect class which implements IDTCommandTarget as well as IDTExtensibility2. Two extra methods are created for this interface:

  • Exec: Executes the specified named command
  • QueryStatus: Returns the current status (enabled, disabled, hidden, and so forth) of the specified named command.

There's also a load of code added to the OnConnection method to create a toolbar button. This is added inside an If statement, checking whether the connect mode of the add-in is ext_cm_UISetup. UISetup indicates that it's the first time the add-in has run, so its commands need to be registered.

Whenever I've used this generated code it has been unreliable, as the connect mode never seems to be UISetup. I took an alternative approach based on an article by Rick Strahl, who sounds like he suffered from similar problems with UISetup.

OnStartupComplete fires every time the add-in loads - this can be used to create the command if necessary, and add the create the menu control. The control can then be removed when the add-in is unloaded, using the OnDisconnection method. This method is used in the DemoAddin project mentioned below.

Another article I found very useful understanding the toolbar/menu side of things was by Carlos J. Quintero.

The other two questions in the wizard are not, as far as I know, relevant for SSMS add-ins.

7. Windows

Adding windows to an add-in relies on exactly the same methods as used for Visual Studio add-ins, using user controls. This is demonstrated in the DemoAddin project.

8. An Example Project

I've created a template project which can be used as a basis for an add-in. It doesn't do much, but it should be fairly easy to get running and illustrates a few things:

  • A message box will pop up when SSMS starts to indicate that the add-in is loaded
  • A toolbar item is added to the Tools menu of SSMS, which opens a new window when clicked
  • An event fires every time you execute SQL from the query window

To be able to run the project, you should check the following settings:

  • Start External Program has the correct path to SqlWb.exe (See section 1)
  • The PostBuildEvent has the correct path to the location of the MSI file 

If these setting are correct then you should be able to run it straight away, step into the code, and start creating your own SSMS add-in.

Total SQL Recall - A Recent Queries (SQL Execution History) SSMS Add-in

Total SQL Recall in actionTotal SQL Recall is an add-in that stores and displays your recently executed SQL, fully integrated with SSMS. It provides the same SQL recall functionality in SQL Server Management Studio that TOAD users have had for years. SQL fragments can be inserted into the current query editor, opened in a new window, or dragged / copied to another environment.

The add-in is displayed as a standard SSMS tool window, so can be docked, auto-hidden or floated as required. The window lists the recent queries, together with their date of execution. The size of the rows and columns can be modified, and if a query doesn't fit then a tooltip will display more details. A filter lets you search your stored SQL queries.

Options are available to start/stop query-storing (useful if you're running some huge scripts and don't want to store them), configure the number of queries stored and adjust how they appear. More advanced options can control the maximum length of query stored and the maximum size of tooltip displayed.

The simplest way to recover a piece of SQL is to double-click on it or drag it into the current query editor. The context menu offers more options including opening in a new window and copying to the clipboard. The context menu also provides access to the options screen and the start/stop feature.

Total SQL Recall can be launched from the View menu - it will be the last item on the menu. The shortcut is Alt-V,O. If SSMS is running when you install Total SQL Recall then you'll have to restart it.

There are four install packages, depending on your O/S & version of SSMS:

It's vaguely possible that there might be a second version of the add-in with a second tab for permanently stored queries. If there are any other features you'd find useful then let me know.

Many thanks to Sean Price for pointing me in the right direction after my initial post on SSMS Add-ins.

The source code for the add-in is available on GitHub.

Drag and drop problems in HTA frames
During our recent migration to HTA we discovered a problem dragging hyperlinks within frames (either <FRAMESET> or <IFRAME>). The problem was that dragging the contents of an <A> element onto a frame caused the link to be loaded in the frame - it was as if the hyperlink were being clicked.

The problem occurs when a drag ends inside a frame, regardless of whether the drag started outside the frame, within another frame, or within the same frame.

The contents of the <A> element don't seem to make any difference either: we first noticed the problem with image hyperlinks but then discovered that text behaved the same way.

The way we've solved the problem is using the ondragstart event. By handling the event it is possible to prevent the user dragging:
ondragstart="return false"
The ondragstart event occurs for both the <BODY> and the <A> involved, so there's some flexibility in where you attach the event handler. If the HTA is using an <IFRAME> then the ondragstart can be attached to the <BODY> of the parent document (the page containing the <IFRAME>). If the system is using a <FRAMESET> then the parent document will not have a <BODY> - there is no solution at the parent level in this case.

With both the <IFRAME> and <FRAMESET> variations you can attach the ondragstart to either the <BODY> or <A> of the child document (the page within the frame), though on large pages you may notice that attaching to the <A> element slows the browser down - using <BODY> is probably a safer bet.

  Parent Document Frame Contents
Normal page Dragging not possible N/A
IFrame Use <BODY> ondragstart Use <BODY> or <A> ondragstart
Frameset No solution Use <BODY> or <A> ondragstart

If you opt to put the ondragstart on the child document then you'll probably want to consider a way of applying it universally, so it affects all pages shown in the frame. In ASP.NET this could be done with a base page class, but the option we took was to use an HTC attached using an existing stylesheet: all our pages use a standard stylesheet, so this was modified to include a reference to the HTC file:

    behavior: url(;
The htc file simply attaches a function to the ondragstart event:
<public:attach event="ondragstart" handler="preventDrag" />
script type="text/javascript">
preventDrag() {
        event.returnValue = false;
One thing to be careful of with the HTC reference in the stylesheet is it's path: the path is from the page using the stylesheet. The safest way to define it is probably as an absolute path or root relative path.

I suspect this solution will need modifying if we ever want to implement AJAX-style drag and drop, but for the moment it's solved our problem.

Using ...WHERE NOT IN (SELECT... with NULLs

I was asked to look at a query today and explain why using a NOT IN clause wasn't returning any rows. Switching the NOT IN to an IN resulted in 38 rows, and removing the clause completely returned 431 rows. So what was happening to the other 393? Surely if a row is not in IN, then it should be in NOT IN?

FROM    T1
        FROM    T2)

T1 contained 431 rows, so without the WHERE clause these were all returned, as expected. T2 contained 38 rows where C2 = C1, so these were returned using the IN clause.

However, the query as written above returned no results. 

It turned out that there were NULL values for C2. Since NULL really means "unknown", SQL Server was unwilling to assume that any of the C1 values were NOT IN the sub-query results.

Although it makes sense when you think about it, I don't remember coming across the NULL = unknown issue in a NOT IN list before. Definitely something to consider when using NOT IN.

Louis Davison explains the problem in more detail.

Why you shouldn't use in HTA

Running an HTA application alongside a browser can hide (or create) some nasty bugs if you're not opening your windows correctly. An HTA application removes almost all of the useful development tools available in a browser window, so it's common for HTA-developers to also use normal browsers, but this requires extra care if you're opening new windows.

I mentioned last month that is not trusted under HTA rules, so window.showModelessDialog or window.showModalDialog should be used instead. In addition, I posted earlier this month about browser windows sharing sessions.

In addition to the aesthetic issues, using becomes a big issue if you try sharing session state between the application and the dialog. Normally you'd expect an error when firing up such a dialog from an HTA, as the session would not be available: opens a standard browser window, unconnected to the HTA, running in a different process, so the dialog page cannot access the same session. At this point you'd normally switch to window.showModlessDialog (which opens a window in the same process as the HTA, so shares the session) and your session-based popup would be fine.

However, if you open the dialog from the HTA using, whilst also running an instance of the application in a normal browser window, then the dialog can appear to work fine. This is because the dialog opens in IE, and IE can decide to use the same process for the dialog as used for the existing browser. Since the dialog and the browser share a process, they share session, so the dialog opens up with access to a session - just not the session that the HTA was expecting it to use.

This is the only time I've seen IE7 sharing a process between multiple windows - as I mentioned in my earlier post, it generally seems to create a process per window - but this just reinforces the importance of thinking about shared processes, and using the correct method to open extra windows from HTA apps.

Learning from your blog's referrals
Digging around in the referrals to a blog can be quite educational, especially for the blog's author.

The referrals list shows the pages from which readers have arrived - pages with links to your site. The list is created from an analysis of the HTTP requests received by the server. Each request has an HTTP header, which can include an optional field called Referer [sic], used by the requesting browser to tell the server what the previous page was.

The referrals list can be particularly interesting to the author because it shows the search criteria that people used when finding a post through a search engine - this is included in the querystring of the referring page. Having access to these criteria allows you to run the searches again (by following the link in the list), which gives you access to other people's search results. If they've used significantly different criteria to you then you may well unearth new pages related to the subject matter that you might have missed when running your own searches.

The only problem with this is that, if you discover something new, you may need to update your original post...
Browsers, processes, cookies and session state
Opening the same web page in multiple browser tabs or windows can cause some serious problems if that page relies on cookies or session state. If you're lucky, the problem will be obvious to the user but it's quite possible that they'll be completely unaware of it until after they've corrupted some data.

The Problem

Imagine the user of a web application, viewing details of Object1. The user wants to compare Object1 with Object2 so opens the details of Object2 in a second window or tab. If the application is storing the "current object id" in session state or a cookie then this value will now correspond to Object2. The user then decides to modify Object1's details, so amends them on the page and saves the changes.

If the application is really badly coded then the save operation could update the record corresponding to the current object id (Object2) with the new details for Object1. Even if it updates the right record, the current id in session state is still wrong - if this id is used to select the data for the next page that the user visits then they will end up with both tabs/windows pointing at Object2.


The problem stems from the fact that multiple tabs and windows can be running in the same process.

Firefox uses the same process for multiple tabs and, by default, the same process for all windows, whether they are launched from Windows or from each other (Ctrl-N style).

IE6 managed it's own processes so you could never be entirely certain about when further processes would be created unless you forced the situation using the -new command line switch. The most common situation I've found is that Ctrl-N creates a window using the existing process, Javascript calls (e.g., use the existing process, but launching IE from Windows creates a new process.

IE7 has abandoned the -new switch, and seems to use a new process for each new window launched from Windows. All tabs within a window, however, run under one process, and spawning windows with Ctrl-N or Javascript commands seems to always re-use the existing process as well.

Cookies and Session State

Sharing a process isn't itself a bad thing. Time and resources can be saved by this approach, but unfortunately a browser's cookies are tied to it's process. If a page is displayed in two tabs or windows running in the same process, then the two instances of the page will share their cookies.

There are two types of cookie. Persistent cookies are saved to disk and kept until their expiry date. Persistent cookies will always be shared between multiple instances of the same page, regardless of whether the pages are running in the same browser process. If the page creates a persistent cookie called "ObjectID" then this will be stored in a file on disk and will be accessible to any other instance of that same page (unless you use a different browser application - IE and Firefox do not share cookies).

Session cookies, on the other hand, are kept in memory and are only available until the browser process ends. If two instances of a web application run in two separate processes then there will be two separate session cookies, but if the two instances are in the same process, then they will share the session cookie.

Furthermore, if the web application is relying on a session cookie to store a session id (the default setup for an ASP.NET web application is to store the ASP.NET_SessionId in a session cookie) then anything in session state will be shared between the two pages: if one of them updates session state then the other will be affected.


What this means for a developer is that it is quite possible that your application will have to cope with multiple copies of the same page running in the same process, sharing cookies. Ideally you should be able to have each page running independently of the others, regardless of them sharing a process.

Normally you can work around the problem by using viewstate. Small objects can be stored directly in viewstate but you shouldn't be sending anything too big down the line to the browser. If your object is more than a simple integer or short string then it will probably be better to generate a GUID and store that in viewstate, using the GUID to access a part of sessionstate which can be kept unique for that instance of the page, regardless of the process-sharing.

In the example we began with, the current object id could easily be stored in viewstate. If there was an object that needed to be persisted for some reason then it would probably be better off in session state, so the second technique would be better.

There are times, however, when viewstate doesn't work. In some situations (for example, setting up dynamically generated controls) the current object id may be required in Page_Init, when viewstate is not available. This was actually the situation which lead to us developing an HTA-based intranet (each instance of an HTA has it's own process, so cookies and sessions are never shared), but HTA is not an option for a normal website.

Probably the best solution, if you're using ASP.NET, is cookieless sessions. In this situation the ASP.NET session id is part of the URL, and is not shared between tabs or windows. This solution works well in the Page_Init situation, but leads to some very unwieldy URLs and has other drawbacks connected to security and absolute linking. It is also an application (or machine) setting, so cannot be used as a last resort only for those few pages that need Page_Init.


In general, viewstate is the perfect solution to the problem. Each instance of a page can keep track of its own state, with no interference from other instances.

When state information is required in Page_Init things get a little more complicated and cookieless sessions are definitely worth considering.


Test Code

A simple page incrementing a counter in session state can be used to demonstrate the problem. Launching new windows with CTRL-N in either browser will default to using the existing process, as will all tabs.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

   Dim x As Integer

   If IsNothing(Session("test")) Then
      x = 1234
      x = CInt(Session("test")) + 1
   End If

   Session("test") = x

   Label1.Text = CInt(Session("test"))

End Sub
Do you want your own database?

Following an excellent post by Jeff Atwood on Coding Horror, I've found some further reading about database change management, a topic I first posted on a few weeks ago, following a session at SQLBits.

Jeff's post is focussed on build scripts but one of the comments is by a guy called Mike Hadlow, who wrote about source-controlling databases a while back. A lot of what he suggests fits in with the change management strategy suggested at SQLBits, but he also recommends that each developer should work on their own database, in the same way that each developer works on their own copy of the code. When a particular change has been unit-tested satisfactorily then the scripts are updated into the repository and the other developers can "get latest" to update their own databases.

It's not something I've ever tried, but I quite like the idea of applying continuous integration ideas to database development.

The second article doesn't say much itself but has links to some more articles about source-control, and a list of useful tools.

Unit testing a database
Database testing is often ignored by development teams, under the assumption that testing the application tests the database sufficiently. Richard Fennell gave an interesting presentation at SQLBits recommending formal unit testing of SQL Server using TSQLUnit, part of the xUnit family.

Although most developers (hopefully!) test database objects as they develop them, the testing is usually informal and undocumented, and rarely repeated. Richard suggested that using a framework such as TSQLUnit encouraged tests that would be consistent and repeatable, i.e. all tests could be performed on a regular basis to ensure that nothing that used to work has been broken by a later change.

TSQLUnit is extremely easy to install. The zip file download contains a SQL file called tsqlunit.sql which installs 5 tables and 10 stored procedures. That's it!

Writing tests is also simple, though I've yet to try testing any very complicated objects. Tests can be grouped together into TestSuites, and fixtures (data required for the test) can be created with a corresponding setup procedure for each TestSuite. Tests are run inside transactions, so any data you insert will be cleared when the transaction rolls back.

If you have the inclination then you can write tests to check any database object, security, static data, etc.

Richard also showed how to use Data Dude for unit testing a database, but it's hard to beat the simplicity of TSQLUnit.

Thanks to Richard for a great talk.
Database Change Management

One of the most interesting sessions at SQLBits was about managing change in databases. We still haven't reached the end-goal of a DBMS that prevents changes to objects until you check them out for editing, but it was the first time I'd heard of a solution which actually sounds solid enough to rely on.

When people talk about source-controlling their database they normally mean storing a set of CREATE scripts for every object in the database (there are some alternatives in this white paper). This approach gives you an audit trail so you can work out when an object changed, who changed it and why. Storing CREATE scripts also allows you to document your design using extended properties - it's dangerous to rely on SQL Server storing these, as they can easily be lost during upgrades.

The source-controlled scripts can be used to create an empty database but obviously aren't much use if you're regularly updating a production database - you can't just drop and re-create tables which have any data.

The main problem, though, is that this approach relies on discipline. If a developer or DBA makes a change to the development database and forgets to change the CREATE script then the source control loses its value.

Another problem is that upgrading the production system still requires a bunch of ALTER scripts for the changed tables. Either the developers have to write these at the same time as they make the changes or, when the system is upgraded, the DBA uses a comparison tool to ensure the production database ends up the same shape as the development one. The first of these involves three parts to keep in synch (database, CREATE script in source control and ALTER script for the upgrade) and the second leaves you in a position where the source controlled CREATE scripts are never checked against either development or production databases - although they may still be useful they could get horribly out of synch.

At SQLBits, András Belokosztolszki presented an enhancement to the process just described, which sounds like it might make the difference. András is the architect of Red-Gate's SQL Compare, so was obviously trying to sell the product, but it seems to be the last piece in the puzzle.

What the new version does differently is to compare databases against scripts. When I read about this feature it didn't strike me as particularly useful, but after talking to András I can see how powerful it is.

Firstly it removes the problem of developers/DBAs forgetting to update the CREATE scripts as they make changes. When the time comes for an upgrade to the production system, SQL Compare can be used to check that the source-controlled scripts reflect the development database. Anything that was omitted can be automatically synchronised, so you can be sure that your scripts are bang up to date.

Secondly there's no need to write the ALTER scripts. SQL Compare can compare the source-controlled CREATE scripts against the production database and can generate (and even apply) the scripts required to synchronise, maintaining any data. The scripts generated by SQL Compare 5 haven't always got dependencies in the right order so I'll be interested to see how version 6 does.

There's still a slight issue of losing incremental changes if an object is updated several times and only the final version is reflected in the script, so the fully source-controlled database remains the target. Until then, this seems like the best solution.

Thanks to András for a very interesting talk.

Thanks also to Simon and Tony for organising SQLBits.

More Posts « Previous page