Pages

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>
    <Version>10.0</Version>

</HostApplication> 

to be:


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

</HostApplication> 


and

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]


10 comments:

  1. Great job ! Exactly what I was looking for :)
    Please note that in my case, at step 12, I had to manually create the "MSEnvShared\AddIns" folders...
    I ll be looking forward to read the next posts ...
    Best regards,
    Raphael

    ReplyDelete
  2. Thank you so much! After reading so many blogs about creating stuff that does not work, this simply works out of the box. And it is debuggable as well!
    Funny thing i noticed was that if i had a dot in my project name then i could not select the "Yes, create Tools menu item...".
    Now to read the rest of your series, looking forward to it.

    ReplyDelete
  3. thank you very much! possible to this can be applied for 2008r2 i am looking for 08r2 addin very very desperate!!

    ReplyDelete
    Replies
    1. Yes and No - the concepts are the same, but the object models and namespaces are different.

      Each version 2005/2008/2008r2/2012 are all slightly different.

      Delete
  4. I've made an update: This works on SQL Server 2012, you just need to update the place where you put your .Addin file. C:\ProgramData\Microsoft\MSEnvShared\AddIns

    ReplyDelete
  5. hi is this thread still active?? i have a small issue i have done exactly the same till step 15 but while running the step 16 it gives me a strange error(same as if open run and enter ssms.exe /?) please can u suggest me what does this mean? any pointers would be much appreciated. i am using ssms 2k8r2.

    ReplyDelete
    Replies
    1. remove command line arg
      /resetaddin MySqlAddIn.Connect
      from projkect debug start option.

      Delete
    2. Hi there I have removed the command arg, as suggested.

      SSMS starts up fine when debugging. The addin however does not load, or at least it doesn,t hit any break points and is not visible in the Tools menu.

      Delete
  6. Hi there, these instructions are for ssms 2012 , so you are going to get odd results for ssms 2008 r2.
    Take the /? out, tts a mistake.

    ReplyDelete