If you are trying out SQL Server 2008 R2 CTP2 and you are a frequent user of Addin’s for SSMS like the SSMS Tool Pack or the SQL Server 2008 Extended Events Addin that I wrote, you will notice almost immediately that the Addin’s will not work and raise a couple of exceptions. This is because as previously mentioned in my blog post about the Devenus SQL.CLR Addin, SSMS Addins are not a supported feature by Microsoft, so there is no guarantee that code will work from build to build of SQL Server. Until recently I had counted myself as lucky and that I had not run into any breaking changes while implementing the SQL Server 2008 Extended Events Addin. Since I am working with the 2008 R2 CTP, one of the first things I did was install the Addin so I could see if any changes were made to Extended Events in the newer release (I didn’t expect any, but perhaps there was a nice surprise waiting, there actually wasn’t), and low and behold immediately I got an exception when SSMS loaded. I thought I’d dig down into this a bit to try and fix the SQL Server 2008 Extended Events Addin I wrote but it’s very clear that Microsoft made some significant changes to the assemblies for SSMS with regard to Visual Studio Integration.
First the ServiceCache no longer exposes a GetObjectExplorer() method. What I did and what most people did was to call ServiceCache.GetObjectExplorer() to get a copy of the IObjectExplorerService object:
IObjectExplorerService objExplorer = ServiceCache.GetObjectExplorer();
The fact that this changed wasn’t necessarily a big deal because you can still get a copy of the IObjectExplorerService from the ServiceCache.ServiceProvider by invoking the GetService() method and passing the IObjectExplorerService Type to it
IObjectExplorerService objExplorer = ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));
Initially I thought that this change would be sufficient to fix the Addin, but I found another breaking change as well. To subscribe to the SelectionChanged event for the Object Explorer Tool Window, the IObjectExplorerEventProvider interface was used to get a copy of the Event Provider from the IObjectExplorerService object.
IObjectExplorerEventProvider provider = (IObjectExplorerEventProvider)ServiceCache.GetObjectExplorer().GetService(typeof(IObjectExplorerEventProvider));
provider.SelectionChanged += new NodesChangedEventHandler(Provider_SelectionChanged);
Unfortunately, there wasn’t a common easy fix to the fact that the IObjectExplorerEventProvider no longer exists at all. I had to do some investigation to figure out what to do about this since handling the SelectionChanged Event was crucial to the Addin functioning properly. Luckily I happened on the fact the the GetService() call for the IObjectExplorerEventProvider type actually returns a new object type of ObjectExplorerService which requires the addition of the Microsoft.SqlServer.Management.SqlStudio.Explorer Assembly to the Addin project. After some debug watching of the local objects, I was able to figure out that the ObjectExplorerService contains a reference to another Service, the ContextService which actually has another Event called the CurrentContextChanged Event which fires when the Selected Node changes:
ObjectExplorerService objExplorerService = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));
ContextService cs = (ContextService)objExplorerService.Container.Components;
cs.ObjectExplorerContext.CurrentContextChanged += new NodesChangedEventHandler(Provider_SelectionChanged);
I noticed a number of other changes along the way to looking at this such as the implementation of a NavigationService, also accessible in the ObjectExplorerService.Container.Components collection though I have yet to figure out how it is used yet. I am sure I have another blog post forthcoming that covers further changes I’ve found while looking at this, but for the time being the above two major changes affect a number of existing Addins and will hopefully be useful to those developers in fixing their code for the newest release of SQL Server.
If you’ve never built a SSMS Addin, and are interested, some important references are:
Building a SQL Server Management Studio Add-in - Jon Sayce
The Black Art of Writing a SQL Server Management Studio 2005 Add-In
If you want some free source code to look at you can find some Addins on Codeplex:
SSMS Addins – Home
Allocation SQL Server Management Studio Add-in – Home
SQL Server 2008 Extended Events Manager
I’ll be posting an updated Addin for SQL 2008 R2 this weekend after some final testing of the implementation