THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

SQL 2008 R2 Breaks SSMS Addins

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[0];
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:

Extend_Functionality_in_SQL_Server_2005_Management_Studio_with_Addins
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

Published Saturday, August 22, 2009 12:41 AM by Jonathan Kehayias

Comments

 

Ashton said:

Jonathan, I am in the process of writing an AddIn for SSMS and would be affected by the above changes.  Have you heard if they have any plans to finally support addins within SSMS?  Some of the SSMS PMs approached the group I worked for about 4 years ago and asked if we wanted support for SSMS addins so I had hoped that it would eventually make it in (or at least no breaking changes would occur...)

August 24, 2009 3:42 PM
 

Jonathan Kehayias said:

Ashton,

Nope, it is still not supported, though there is an open Connect item for it:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=265567&wa=wsignin1.0

If you plan to develop for 2008 and R2, you will have two code branches since the changes occurred inside of Assemblies, and no version numbers changed from 2008 to R2, despite there being different versions.

August 24, 2009 4:15 PM
 

ErikEJ said:

Jonathan, this is extremely useful info. Thanks for the heavy lifting!

August 25, 2009 11:55 AM
 

Mladen said:

Just to let everyone know that this method isn't 100% reliable.

i've played with this extensively and haven't found a reliable way in R2 to tackle this issue. The object explorer appears to be loading in async mode in R2 and it doesn't have any IsLoaded event you could subscribe to.

The problem is that the Componet Collection doesn't always have a ContextService at the startup.

so for now R2 is a no go for add-ins that use Object Explorer in any way.

as for the official add-in supprot it defintely won't be in R2. and by looking at the API itself i can't see it being realisticaly done in later version too.

September 11, 2009 8:21 AM
 

robert said:

on my system the sql08r2 call recommended above to retrieve the ContextService object throws an invalid cast exception.  Anyone else getting this and found a work around for retrieving that object in order to wire up the selection changed event handler?

//IObjectExplorerService objectExplorer = ServiceCache.GetObjectExplorer();

//IObjectExplorerEventProvider provider = (IObjectExplorerEventProvider)objectExplorer.GetService(typeof(IObjectExplorerEventProvider));

//provider.SelectionChanged += new NodesChangedEventHandler(Provider_SelectionChanged);

ObjectExplorerService objectExplorer = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));

ContextService contextService = (ContextService)objectExplorer.Container.Components[0];

INavigationContextProvider provider = contextService.ObjectExplorerContext;

provider.CurrentContextChanged += new NodesChangedEventHandler(Provider_SelectionChanged);

September 14, 2009 2:16 PM
 

Jonathan Kehayias said:

Robert,

See Mladen's comment above that was pending moderation (I've been busy and didn't approve it).  

September 15, 2009 7:21 AM
 

robert said:

got existing addin working on sql08r2 ctp2 using following OnConnection and Provider_SelectionChanged implementation changes

public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)

{

   //_applicationObject = (DTE2)application;

   _addInInstance = (AddIn)addInInst;

   _applicationObject = (DTE2)_addInInstance.DTE;

   // do something with the application, e.g. reference the Commands

   Commands2 commands = (Commands2)_applicationObject.Commands;

   //IObjectExplorerService objectExplorer = ServiceCache.GetObjectExplorer();

   //IObjectExplorerEventProvider provider = (IObjectExplorerEventProvider)objectExplorer.GetService(typeof(IObjectExplorerEventProvider));

   //provider.SelectionChanged += new NodesChangedEventHandler(Provider_SelectionChanged);

   // search ServiceCache.ServiceProvider.GetService ->

   // http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/22/sql-2008-r2-breaks-ssms-addins.aspx

   ObjectExplorerService objectExplorer = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));

   //ContextService contextService = (ContextService)objectExplorer.Container.Components[0];

   //ContextService contextService = (ContextService)ServiceCache.ServiceProvider.GetService(typeof(ContextService));

   // for some reason calling GetSelectedNodes forces to add ContextService to ObjectExplorerService.Container.Components

   int count = objectExplorer.Container.Components.Count;

   int nodeCount; INodeInformation[] nodes;            

   objectExplorer.GetSelectedNodes(out nodeCount, out nodes);

   count = nodeCount; count = nodes.Length;

   count = objectExplorer.Container.Components.Count;

   ContextService contextService = (ContextService)objectExplorer.Container.Components[1];

   // or ContextService contextService = (ContextService)objectExplorer.Site.Container.Components[1];

   INavigationContextProvider provider = contextService.ObjectExplorerContext;

   provider.CurrentContextChanged += new NodesChangedEventHandler(Provider_SelectionChanged);

}

private void Provider_SelectionChanged(object sender, NodesChangedEventArgs args)

{

   INavigationContextProvider provider = (INavigationContextProvider)sender;

   //INodeInformation[] nodes;

   //int nodeCount;

   ////IObjectExplorerService objectExplorer = ServiceCache.GetObjectExplorer();

   //ObjectExplorerService objectExplorer = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));

   //objectExplorer.GetSelectedNodes(out nodeCount, out nodes);

   //INodeInformation node = (nodeCount > 0 ? nodes[0] : null);

   INodeInformation node = (args.ChangedNodes.Count > 0 ? args.ChangedNodes[0] : null);

September 15, 2009 2:38 PM
 

Jonathan Kehayias said:

robert,

Now that makes why it works for me make more sense.  The addin code I have doesn't use the ObjectExplorer until the Addin is actually activated, which loads a control in a toolwindow.  The control's OnLoad Event performs an update of its TreeView by calling:

       public static INodeInformation ObjectExplorerSelectedNode

       {

           get

           {

               int count;

               INodeInformation[] informationArray;

               ServiceCache.GetObjectExplorer().GetSelectedNodes(out count, out informationArray);

               if (count > 0)

               {

                   return informationArray[0];

               }

               return null;

           }

       }

So I always have the correct Context necessary.  Makes much more sense now.  Thanks for the update Robert.

September 15, 2009 3:21 PM
 

Charles Rex said:

Hi,

You are geniuses.

I never understood how and what tools  you use to 'discover' the undocumented functionality of SQL Server Management Studio.

You must have smart tools. Can you share some of your working methods with such tools?

Thanks.

September 17, 2009 5:08 AM
 

Rob Roll said:

Anyone know when the fall ctp will be available?

I've checked at sql2008r2.com but it's still the august ctp.

October 31, 2009 5:04 PM
 

Muhmud Ahmad said:

Thanks Jonathan/robert, saved me a lot of time.

May 5, 2010 4:54 AM
 

Reagan Boone said:

Is a new version of the addin available?  I just installed SSMS 2008 R2, and it broke the addin as described above.

August 16, 2010 3:56 PM
 

Jonathan Kehayias said:

Reagan,

The last build on the Addin site has the code fix included in it.  I am using that build on SSMS 2008 R2 without issues.

August 16, 2010 10:09 PM
 

Bilal Nazer. said:

ObjectExplorerService objExplorerService = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));

ContextService cs = (ContextService)objExplorerService.Container.Components[0];

cs.ObjectExplorerContext.CurrentContextChanged += new NodesChangedEventHandler(Provider_SelectionChanged);

Hi Jonanthan,

            I have tried the above code in my SSMS Addin for SQL Server 2008 R2 but in line2 where the casting to Context Service happend is throwing an exceoption that IObjectExplorerService cannot cast to Context Service.

Do you have any idea why things happening wrong in my addin?

Please advice.

Thanks,

Bilal Nazer.

March 1, 2011 11:31 AM
 

Bilal Nazer said:

And I am not using SQL Server 2008 R2 CTP and I am using SQL Server 2008 RTM

Version Information from About Box:

-------------------------------------

Microsoft SQL Server Management Studio 10.50.1600.1

Microsoft Analysis Services Client Tools 10.50.1600.1

Microsoft Data Access Components (MDAC) 6.0.6001.18000

Microsoft MSXML 2.6 3.0 5.0 6.0

Microsoft Internet Explorer 7.0.6001.18000

Microsoft .NET Framework 2.0.50727.3615

Operating System 6.0.6001

Actual Exception:

Ubale to cast object of Type Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService to type Microsoft.SqlServer.Management.SqlStudio.Explorer.ContextService

March 2, 2011 7:39 AM
 

Bilal Nazer said:

I have Fixed the issue.  

Thanks Robert you saved my time.

March 4, 2011 8:49 AM
 

Karl said:

Has anyone tried this out with the latest CTP of SQL Server Denali?

It appears that the ServiceCache object has disappeared - well, it's still there but it's null - and I can't figure out where to get a copy of the IObjectExplorerService.

March 4, 2011 12:09 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement