January 2008 - Posts

Customising the SSMS Table Designer

Rick Strahl has a great post on making the SSMS Table Designer more keyboard-friendly by removing the need to have two panes:

Customised Table Designer 

Uninstalling an SSMS Add-in

One thing I forgot to mention in my recent posts on SSMS add-ins was how to uninstall them. Normally this can be done using the standard Windows uninstall methods (Add/Remove Programs, uninstall the MSI, etc) but sometimes during development you may find you're stuck with an unwanted add-in.

The simplest thing to do in this case is what SSMS does itself when an add-in fails to initialise properly: simply remove the registry setting that causes the add-in to be loaded. Delete the key for the add-in, which will be something like this:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\AddIns\Namespace.Class

This will guarantee that the add-in is never loaded again, though obviously it doesn't remove the other registry settings or the add-in files themselves.

The other problem it can leave you with is unwanted menu and toolbar items in SSMS. These can be located and removed using a similar technique to that described in section 4 of my post on building an add-in. Deleting a command should remove any menu items connected to the command.
 

Third-party IFrames in HTA Applications

I've posted before about setting Application = "Yes" on iframes in HTA applications, but if the iframe is rendered by a third-party component then this may not be straightforward. If the iframe runs JavaScript accessing it's parent window then somehow the iframe must be modified to pass the security rules enforced by the HTA model.

Using the onload event of the <BODY> element it's possible to set the Application attribute of an iframe, but this is not enough to make the iframe HTA-friendly.

Refreshing the contents of the iframe (using iframe.src = iframe.src) doesn't work either. I'm guessing that the security aspects of an iframe are baked into the element when the browser first renders it.

The solution is to use the onload event of the body to rebuild the entire iframe element:

if (document.getElementById('spIFrame')) {

  // get reference to the iFrame
  var iFr = document.getElementById('spIFrame');

  // set the application attribute of the iFrame (so it works in HTA)
  iFr.application = 'Yes';

  // rebuild the iFrame to make sure Application="Yes" is active
  iFr.outerHTML = iFr.outerHTML;
}
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))
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:

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:

body
{
    behavior: url(PreventDrag.htc);
}
The htc file simply attaches a function to the ondragstart event:
<public:attach event="ondragstart" handler="preventDrag" />
<
script type="text/javascript">
    function
preventDrag() {
        event.returnValue = false;
    }
</script>
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.