Extend Functionality in SQL Server 2005 Management Studio with Add-ins
page 1 of 12
Published: 07 Aug 2007
Abstract
Ameet Phadnis had to go through some trials and errors while building the SQL SP Generator software for his company. In this Article he explains how you can create Add-Ins for SQL Management Studio.
by Ameet Phadnis
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 82787/ 90

Introduction

My entire career I have been looking for ways to automate the development process for developers. The urge to find new solutions might be coming from my laziness to keep on doing the same stuff over and over again. Around 4 years ago I had designed a way to create Stored Procedures on the SQL Server database. You can find more information on the Stored Procedure Generator by viewing my article titled Speed up with SQL Stored Procedure Generator. But that process was too manual. I had to keep in mind what kind of parameters I needed to pass to get the desired results. Again, I was tired of that too. So, then I started thinking about writing a Windows.NET program to resolve this issue.

By then Microsoft had released the SQL Server 2005. This product included a GUI called SQL Server Management Studio (SSMS). The whole idea behind SSMS was that it was based on Visual Studio 2005. So, I started wondering whether I could write an Add-In as we do for Visual Studio 2005. I was so wrong with this assumption. I did lot of research and most of them pointed out, "Microsoft doesn't support SQL Server 2005 add-ins." Then they came with an announcement that they would support it fully in SQL Server 2008. So, I started digging more. My co-workers and I were determined to write a Stored Procedure Generator Windows Application. But I was determined to write a SQL 2005 add-in which would not require the user to enter the database connection string. If any of the readers have used all the tools available for SQL Stored Procedure Generators then you might have realized that you have to enter the database connection string in order to get table information.

My perseverance paid off and one day I stumbled onto a code on the web. I have not been able to find it any more. It was in a blog entry by some Russian genius. But there was no code explanation. So, I had to do some trial and error while we started coding for our SQL SP Generator. So, here I am trying to explain the whole process on how you can extend the functionality in SQL Management Studio with add-ins. Please keep in mind that this applies only to SQL Server Management Studio 2005 and the process might change with SQL Server Management Studio 2008.


View Entire Article

User Comments

Title: How to Add 3rd Party software in SQL Management Studio   
Name: Nilesh Gambhava
Date: 2011-11-05 11:01:41 PM
Comment:
I am using Tools4SQL Stored Procedure Generator. Every time I have to open it separately and work on it. Can I add it to SQL Server Management Studio some how so that I can work together?
Title: ServiceCache not available in SSMS R2   
Name: Santiago Perez
Date: 2011-09-15 3:54:42 PM
Comment:
I understand that ServiceCache is no longer available in SSMS 2008 R2. Can you please explain an alternative approach/objects(s) for this environment?

Thanks
Title: Setup Package Explaination   
Name: Rob
Date: 2011-08-08 9:12:50 AM
Comment:
I need to build a setup package using WiX. Does anybody know how the setup actually works so that I can replicate it in wix?
Title: source code?   
Name: George
Date: 2010-09-20 10:32:35 AM
Comment:
when I follow up the code, I got error: 'Getservice' is not a member of 'ObjectExplorer', any idea? many thanks.
Title: SQL Server 2005 Add In   
Name: Yatin
Date: 2009-11-13 6:22:13 AM
Comment:
Article is great but can i have this article examples in C#.NET instead of VB.NET ? My Email ID : yatin_patadiya@yahoo.co.in
Title: SQLServer2005AddIn object   
Name: Aziz Kapadia
Date: 2009-09-08 3:02:01 AM
Comment:
Hi Ameet,

Nice article, how ever I could not find object SQLServer2005AddIn any where. Can you please help me out?
Title: sql formatter add-on for SSMS   
Name: James
Date: 2009-07-31 3:10:01 AM
Comment:
Hi Ameet,

Thanks for your nice article, I have created a sql formatter add-on for SSMS after reading your article, it's really very helpful.
You may have a try of it here:
http://www.dpriver.com/sqlpp/ssmsaddin.html

Let me know if you need a license to use it in your daily work.

--James
Free Online SQL Formatter:
http://www.dpriver.com/pp/sqlformat.htm
Title: RegAsm addin.dll issue   
Name: maco
Date: 2009-07-24 10:52:47 PM
Comment:
Hi All,
I have a strange problem on regasm registration. based on special requirement, I need to let the add-in registered by administrator and used by other desk users.The problem is only administrator can use the SSMS add-in successfully, failed to other desk users.

if SSMS build the userprofile for each user? how to deal with this issue?

any help will be appreciated.

urs mareinke
Title: Sql server management studio express   
Name: Angel
Date: 2009-03-26 5:49:25 AM
Comment:
hi, i downloaded SSMS 2005 from microsoft site. Also i am reading it's online tutorial provided my microsoft. Now,I am in trouble tutorial says something and SSMS says something. viz.,i could not get File > New > New Project (i could not get new project) and Solution Explorar. Prior to it I have installed MXML6 and Visual Studio 2005, and Sql server express edition.

what should i do now.
If anybody have anysolution or have the set of SSMS05 that have both Solution Explorar and New Projects then pls. send me the link at my emai id.

angel_is_yours@ymail.com
Title: Relgolook , archival, SME, email management, Information Management, Social Network, SaaS, SOA   
Name: vahila
Date: 2009-03-05 6:03:16 AM
Comment:
Relgolook is a productivity application for outlook users. Archival and email management for organizing and archiving relationships. Managed service provides online referral campaigns, online surveys, online polls, online test.
Title: Register for COM Interop   
Name: Charles Rex
Date: 2008-09-21 5:41:27 PM
Comment:
Hello,

The add-in must be registered before it can be used

regasm MyAddIn1.dll /codebase

or click project's Properties option from the Project menu, select the Build (C#) or Compile (VB.NET) page, and select the Register for COM Interop check box
Title: Quick Questions   
Name: Alex
Date: 2008-07-22 2:17:40 PM
Comment:
How can i get the text and/or selected text from the active window. I need to retrieve the code that was executed
Title: Really hepful   
Name: Viktar Karpach
Date: 2008-04-15 11:29:13 PM
Comment:
I want to create addin for object browser, so it would have better filtering possibilities.
Title: Tanks   
Name: Harjit Singh
Date: 2008-04-10 2:07:38 AM
Comment:
I am a developer of .net, recently I did some test on add-in for SSMS. Thank you for the great article, it's very helpful for me.

When I try my test, I found using Node.GetService(typeof(IMenuHandler)), it returns a internal class DefaultMenuHandler, which implements interface
HierarchyObject IMultiSelectMenuHandler IMenuHandler IWinformsMenuHandler. In your article, you converted it to HierarchyObject to add a new menu item. And I want to get the existing menu item in the context menu. So I convert it to IWinformsMenuHandler. And use GetMenuItems() supplied by IWinformsMenuHandler, this function will return IList [INavigableItem]. Things go well by now.
Title: subkriskat   
Name: subkriskat
Date: 2008-03-21 10:48:26 AM
Comment:
This is a very good article ,which customizing my sql .
But ,where can i get the reference for the SQLServer2005AddIn & SQLObjectNode
Thanks in advance
Title: A question about IMenuHandler   
Name: Daliang Zhang
Date: 2007-11-04 10:06:00 PM
Comment:
A question about IMenuHandler

Hi Ameet

I am a developer of .net, recently I did some test on add-in for SSMS. Thank you for the great article, it's very helpful for me.

When I try my test, I found using Node.GetService(typeof(IMenuHandler)), it returns a internal class DefaultMenuHandler, which implements interface
HierarchyObject IMultiSelectMenuHandler IMenuHandler IWinformsMenuHandler. In your article, you converted it to HierarchyObject to add a new menu item. And I want to get the existing menu item in the context menu. So I convert it to IWinformsMenuHandler. And use GetMenuItems() supplied by IWinformsMenuHandler, this function will return IList [INavigableItem]. Things go well by now.

But when I try to enumerate menu items in IList, I found a weird thing. For example, I click a stored procedure node for the first time, GetMenuItems() return a IList with count equals to 1, but there should be about 12 menu items in the context menu; then I click another stored procedure node, GetMenuItems() return a IList with count equals to the correct menu items count. I don't know why. Anyway, since I get those menu items, I try to do some modification on them. So I try to modify the text of a menu item, try to disable a menu item, but all failed. there is no change when the context menu popup.

Have you ever seen this issue, could you share with my your experience, thanks in advance.

Daliang Zhang
Best Regards
mail:zdliang1981@gmail.com
Title: A question about IMenuHandler   
Name: Daliang Zhang
Date: 2007-11-04 10:04:28 PM
Comment:
When I try my test, I found using Node.GetService(typeof(IMenuHandler)), it returns a internal class DefaultMenuHandler, which implements interface
HierarchyObject IMultiSelectMenuHandler IMenuHandler IWinformsMenuHandler. In your article, you converted it to HierarchyObject to add a new menu item.
Title: A question about IMenuHandler   
Name: Daliang Zhang
Date: 2007-11-04 10:02:21 PM
Comment:
Hi Ameet

I am a developer of .net, recently I did some test on add-in for SSMS. Thank you for the great article, it's very helpful for me.
Title: For Sam and Mark   
Name: A
Date: 2007-10-24 11:36:13 AM
Comment:
If you notice the registry entry already is mentioned in the sections for Registry. Also the debugging explains how you can setup the application for debugging.

Thanks
Title: Additional info that may help others   
Name: Mark
Date: 2007-10-24 10:54:13 AM
Comment:
\
\
\
\
\
\
\
\
Title: Thanks; but Results To?   
Name: Daniel Macey
Date: 2007-10-20 11:13:58 PM
Comment:
Good to find someone has written about this.

Thanks!


I wanted to provide my own output option (Results To) but by the looks of SQLEditors.dll and the Microsoft.SqlServer.Management.QueryExecution namespace it is impossible.

Any comment on what I am trying to do would be appreciated.
Title: Great Article   
Name: Sam
Date: 2007-09-11 9:11:35 PM
Comment:
Hi,
I hope you can shine some light on this. I am interested in debugging my add-in. in the mean time, i am not able to debug. I noticed my add in is never been loaded. Do i need to do the extra step by adding a register entry.
P.S I don't have the AddIn folder there, but i could create it.
(I am using Management Studio Express).

Thanks,
Title: SQLServer2005AddIn?   
Name: kkam
Date: 2007-08-23 9:28:42 PM
Comment:
Hi,
I'm trying to follow your article and code samples to give me a start on my first SSMS add-in. In Listing 4 you define your incomming parameter as "SQLServer2005AddIn". What am I missing...I can't see where that comes from.

Thanks.
Title: Good Job!   
Name: Kelly
Date: 2007-08-08 6:51:44 AM
Comment:
You rock,Ameet -- nice job!
Title: Wow   
Name: W
Date: 2007-08-07 4:56:41 PM
Comment:
The SQL SP Generator you guys developed works great.

Thanks
Title: Extend Functionality in SQL Server 2005 Management Studio with Add-ins   
Name: T
Date: 2007-08-07 3:32:02 PM
Comment:
What class are you looking for?
Check the Context Menu Addition Class
The Menu class to perform actions
sections to get more information.

The author has just put down the basics. They won't reveal the code as they own the code. Most of the stuff is given in the article itself.
Title: Extend Functionality in SQL Server 2005 Management Studio with Add-ins   
Name: Hiren
Date: 2007-08-07 3:10:52 PM
Comment:
I was really interested to know about SQL 2005 extenbility, but it would help more clear if you explain more about creating class that have been used in this sample.
Title: Extend Functionality in SQL Server 2005 Management Studio with Add-ins   
Name: Sanket Terdal
Date: 2007-08-07 9:48:26 AM
Comment:
Very nice article.The step by step demo makes it easier to understand.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-04-27 6:22:45 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search