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:
- New Project dialog: Select Extensibility (under "Other Project Types") then Visual Studio Add-in, and launch the wizard
- Wizard Page 1: Select your language
- Wizard Page 2: Select Microsoft Visual Studio 2005
- Wizard Page 3: Fill in name & description
- Wizard Page 4 & 5: Ignore these options for the moment
- 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))
Next
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
AddHandler _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:
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.