Wednesday, 17 August 2011

How to write a SQL Server Management Studio Add-in for Denali (CTP3) (Part 1)

Writing a SSMS Add-in is not for the faint hearted or those with weak constitutions, but for those who persevere the rewards are small and fleeting, yet worth while.

This post is for people who know roughly what they are doing, so without any more non-sense, here we go.

You will need;
                       Visual Studio 2010 (I have Premium - I've not tried other editions)
                       SQL Server Denali CPT3 (Should work with CTP1 and CTP2 - but I've not tried)
1) Start up VS2010

2) Create a new project, a Visual Studio Add-in, you should find this under 'Other Project Types \ Extensibility'  (You may not have this project type, do not panic! I will make this source available)

3) Give the project a sensible name - like MyAddin1 :-)

4) The Add-in Wizard should start, 'Click Next'

5) Pick you language of choice, I picked 'Create an Add-in  using C#' (C++/CLR and C++/ ATL are both greyed out for me) - Click Next.

6) Un-Select 'Microsoft Visual Studio 2010 Marcos' - Click Next.

7) Give your Add-in a sensible name and description. - Click Next.

8) Tick 'Yes, create a Tools menu Item...' and 'I would like my Add-in to load...' - Click Next.

9) Its up to you if you want a 'Help About' box. - Click Next.

10) Click Finish.

You are almost ready, we just need to sort out the Add-in registration.

Your new Add-in will be already setup to be a Visual Studio 2010 Add-in.
We just need to customise it a bit, so that SSMS recognises it.

11) VS2010 has created you two .Addin files, delete the 'MyAddin1 - For Testing.AddIn'

12) Locate the 2nd .Addin file, it will be somewhere like; c:\users\XXXX\documents\visual studio 2010\Projects\addin\MyAddin1\MyAddin1\MyAddin1.AddIn
Move it to C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns

This is the folder that SSMS is looking for Add-in files.
[Update 19/08/2011: You may need to create the MSEnvShared\AddIns path.  Thanks to Raphael for spotting this]
[Update 19/05/2012: For the final release of SQL Server 2012, the .Addin file must go in the C:\ProgramData\Microsoft\MSEnvShared\AddIns folder]13) You can now delete the MyAddin1.Addin from your project.

14) Edit the C:\Users\Public\Documents\microsoft\MSEnvShared\AddIns\MyAddin1.Addin' file.
Change the top section from :

<HostApplication>   <Name>Microsoft Visual Studio</Name>


to be:

<HostApplication>   <Name>Microsoft SQL Server Management Studio</Name>



Update the Assembly to be the full path to your DLL.
<Assembly>c:\users\XXXXX\documents\visual studio 2010\Projects\addin\MyAddin1\MyAddin1\bin\Debug\MyAddin1.dll </Assembly>

14) Update the Project Properties, in the Debug section, update Start external program to be C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe (Of course, update this if you have installed Denali somewhere else)

15) Turn off P-Invoke warnings. Press CRLT + ALT + E - In Managed Debugging Assistants, find PInvokeStackImbalance, untick it.

16) Add a break point to the Connect() line, Hit F5 and run for the hills - because you should have a SQL Server Management Studio Add-in working.

The Add-in doesn't do very much yet, I will save that for another post. 

But you have an Add-in running, which was easier to write than Add-ins for SQL 2005 and 2008.

The only tricky, bit was the location and contents of the .Addin file.

Have fun writing your own Add-ins!!
[Updated 19/08/2011: I’ve got to say thanks for Mladen Prajdić for the telling me what the version needs to be in the XML]