Mark Gilbert's Blog

Science and technology, served light and fluffy.

SSMS Scripter Internals Part 2 of 3: “Plugging In”

In Part 1 of this series we looked at how the Scripter uses SQL Management Objects (SMO) to script out DROP and CREATE statements for each object selected.  Originally, I wrote the Scripter as a standalone executable, just so I could get something up and running quickly.  The next step was to integrate the Scripter with SQL Server Management Studio (SSMS) as a plug-in.

I wanted the scripter to appear on the context menus for various objects in the SSMS hierarchy:

  • Database
  • “Views” folder
  • Individual views 
  • “Programming/Stored Procedures” folder 
  • Individual stored procedures 
  • “Programming/Functions” folder

I discovered two interesting points about building an SSMS Plug-in.  First, I could find no official documentation on Microsoft’s site (or any other site for that matter) about how to write one of these.  Second, the classes used to write the plug-in for SSMS appear to be the same ones that would be needed to write a plug-in for Visual Studio 2005.

What I did find was the source code for another SSMS plug-in called the Enisey Data Scripter (http://www.codeproject.com/useritems/enisey.asp).  This plug-in was designed to script out data from tables, and as a result added itself as a context menu item to the Table objects in SSMS.  It seemed like as good a starting point as I was going to get.

There were several project settings that had to be specified in order to get various things to work.  These were discovered partially by examining the Enisey Data Scripter, and partially through trial-and-error:

  • Application / Application Type of “Class Library” 
  • Compile / “Register for COM interop” is checked 
  • Debug / Start External Program of “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe” 
  • Project References: 
    • Extensibility.dll – provides access to the Extensibility namespace 
    • Microsoft.SqlServer.SqlTools.VSIntegration – provides access to the Microsoft.SqlServer.Management.UI.VSIntegration namespace.  This required me to add the following path to the Reference Paths: “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\”. 
    • ObjectExplorer.dll – provides access to the Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer namespace.  This required me to add the following path to the Reference Paths: “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\”.  Additionally, the assembly was called “SQL Server Object Explorer Package” in the references dialog, not ObjectExplorer.dll as you might first expect. 
    • SqlWorkbench.Interfaces.dll  – provides access to the INodeInformation and IObjectExplorerService interfaces.  This required me to add the following path to the Reference Paths: “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\”.  Additionally, it was called “SQL Server Object Explorer Interface Package” in the references dialog, not SqlWorkbench.Interfaces.dll as you might expect.

Once these changes were made, I had to add the following registry key:

Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\AddIns\SSMSScriptor.Plugin]
“Description”=”Copyright (c) 2006, Mark Gilbert, mgilbert@blue-granite.com”
“FriendlyName”=”SQL Server Management Studio Extension”
“LoadBehavior”=dword:00000001

SSMScriptor.dll” is the filename of my class library when it builds, so the key had to be named “SSMScriptor.Plugin”.  Additionally, the “AddIns” folder that it resides in didn’t exist on my machine initially, but its parent “Shell” folder did.

Once all of these settings have been made, we can move on to the code itself.  After studying the Enisey scripter intently for obscene amounts of time (ok, it was only a couple of hours, but that’s long in computer-years), I found three classes that seemed to be most relevant to the task of building a plug-in.  These three classes have the following equivalents in my Scripter: Plugin.vb, MenuNode.vb, and ScripterMenuItem.vb.  The main entry point is PlugIn.vb.

The PlugIn class implements the IDTExtensibility2 interface.  There are several event handler methods that are included with this interface, but the only one that the Scripter uses is OnConnection.  This method registers a custom handler called OnSelectionChanged, which fires when the user right-clicks on an object in the tree to bring up its context menu.  If the item selected is a valid object (see the “valid” list at the beginning of this post), then the Scripter menu item is added to that menu using the shared function MenuNode.GetInstanceOnSelectedNode.  A NodesDict dictionary object is used to track which objects have had the menu item loaded for it, and prevents the GetInstanceOnSelectedNode function from adding a second copy of the item to the menu.

The magic of determining whether the current node is a “valid” one or not is done in the MenuNode class constructor, which is invoked by GetInstanceOnSelectedNode.  This evaluates the currently selected node’s Context property.  There is a block of comments near the beginning of the constructor which gives an example of the Context value for each of the nodes that the Scripter can attach to.The Context value is examined, and the appropriate private Boolean variable is set to indicate the type of node that is currently selected:

‘ Set some specific internal variables to denote the node’s type.
Me
.m_blnIsSproc = blnDoServerDatabaseElementsExist AndAlso _
    blnDoObjectNameSchemaElementsExist AndAlso _
    strCurrentContext.Contains(“/StoredProcedure[@Name”) Me
.m_blnIsSProcsFolder = blnDoServerDatabaseElementsExist AndAlso _
    strCurrentContext.Contains(“/Folder[@Name=’StoredProcedures’ and @Type=’StoredProcedure’]”) 
Me.m_blnIsView = blnDoServerDatabaseElementsExist AndAlso _
    blnDoObjectNameSchemaElementsExist AndAlso _
    strCurrentContext.Contains(“/View[@Name”)

Next, the Server and Database names (which are included in every node Context value at the database level and lower in the hierarchy) are extracted, and saved in private variables:

Me.m_strServerName = niCurrent.Context.Split(“‘”)(1)
Me.m_strDatabase = niCurrent.Context.Split(“‘”)(3) 

Finally the type and name of the object currently selected are saved in another pair of private variables:


ElseIf
(Me.m_blnIsSProcsFolder) Then
    Me.m_strObjectName = Common.ALL_OBJECTS
    Me.m_otObjectType = Common.ObjectTypeEnum.StoredProcedures

    ‘ If the menu has already been registered for the Stored Procedure folder, then don’t do it again.
    If
(Not plgCurrent.IsMenuRegisteredForSProcFolder) Then
        Me.AddScripterMenuItem(niCurrent)plgCurrent.IsMenuRegisteredForSProcFolder = True
    End If 
ElseIf
(Me.m_blnIsSproc) Then
    Me.m_strObjectName = niCurrent(“Name”).ToString()
    Me.m_otObjectType = Common.ObjectTypeEnum.StoredProcedures

    ‘ If the menu has already been registered for a specific stored procedure, then it will be registered for all of them
    If
(Not plgCurrent.IsMenuRegisteredForSProcNode) Then
        Me.AddScripterMenuItem(niCurrent)plgCurrent.IsMenuRegisteredForSProcNode = True
    End If 

At this point, the user merely sees a new item in the context menus for whatever they’ve right-clicked on.  When the user actually selects the Scripter from the context menu, the third class, ScripterMenuItem, comes into play.ScripterMenuItem inherits from ToolsMenuItemBase, and as a result has two overridden methods: Invoke and Clone.  The latter is not valid for the Scripter, and as a result merely throws an exception.  The former is called when the user clicks on an item in the context menu, and does the work of instantiating the SSMSScriptor form, and passing it the Server, Database, Object Type, and Object Name property values for the object currently selected to the form’s constructor.

In the third and final part of “SSMS Scripter Internals”, we’ll look at the splash screen used with SSMS Scripter, and how threading is leveraged to make it work. 

Advertisements

December 18, 2006 Posted by | SQL Server, Tools and Toys | 6 Comments

SSMS Scripter Internals Part 1 of 3: “A-scripting we will go”

The first part of the SSMS Scripter that I’d like to explore is the use of SQL Management Objects (SMO).  SMO is the successor to SQL Data Management Objects (SQL-DMO), and is new to SQL Server 2005.  This collection of .NET assemblies allows you to programmatically control SQL Server.

To use these objects, I needed to add the following references to my project:

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.SqlEnum

Finding these was very straightforward: the respective assemblies were all named as above (for example “Microsoft.SqlServer.Smo.dll”), and were found in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\ folder on my machine.To generate the DROP and CREATE statements, we first need to create a Microsoft.SqlServer.Management.SMO.Scripter object.  This object will be configured to generate a particular kind of script (in our case, either the “drop object” script, or the “create object” script), and then used for the objects that the user selects to script out.The Scripter object is used in the SSMSScriptor.btnGenerate_Click event procedure:


‘ First, collect the DROP statements
Me.InitializeProgressBar(“Generating DROP Statements (Step 1 of 2):”)
Me
.m_scrCurrent = New Scripter(Me.CurrentServer)
Me
.m_scrCurrent.Options.ScriptDrops = True
Me.m_scrCurrent.Options.IncludeIfNotExists = True
strScripts &= Me.GenerateScripts(Me.tvwDBObjects.Nodes)
Me.m_scrCurrent = Nothing

‘ Now, collect the CREATE statements
Me
.InitializeProgressBar(“Generating CREATE Statements (Step 2 of 2):”)
Me
.m_scrCurrent = New Scripter(Me.CurrentServer)
Me
.m_scrCurrent.Options.PrimaryObject = True
strScripts &= Me.GenerateScripts(Me.tvwDBObjects.Nodes)
Me
.m_scrCurrent = Nothing

The Scripter class constructor takes a single parameter for a Microsoft.SqlServer.Management.Smo.Server object.  This object has been initialized in the SSMSScriptor class constructor:


Me.m_svrCurrent = New Server(Me.ServerName)
Me
.m_svrCurrent.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql
If
(IsNothing(Me.m_svrCurrent)) Then Throw New Exception(“An error occurred trying to establish a connection to database ‘” & Me.DatabaseName & “‘.  Please check the database name and make sure you have the appropriate permissiont to connect to it.”)

This code assumes that the current user has permissions to the named server via Windows authentication.  Pay particular attention to the SqlExecutionModes assignment as well.To generate the “DROP” statements, we need to set two properties – ScriptDrops and IncludeIfNotExists.  The former generates the “DROP …” statement, while the latter (somewhat paradoxically) wraps that in an “IF EXISTS” conditional.

Once all of the DROP statements have been collected, it’s time to generate the CREATE statements.  The Scripter object is re-instantiated, and this time the PrimaryObject property is used. All of the above is merely setting up for the actual work of scripting out the objects.  That task is done using the ToScript method of the ScripterTreeNode class.  This class inherits from the built in TreeNode class, and adds a few properties specific to the Scripter.  In particular, each object added to the tree view has an “URN” property stored.  “URN” is of type Microsoft.SqlServer.Management.Smo.Urn, and stands for “Uniform Resource Name”.  This property is used by the ToScript method to generate the requested script for the current object:

 

Public Function ToScript(ByVal scrCurrent As Scripter) As String
    Dim smoObjects(1) As Urn

    Dim scScripts As StringCollection

    Dim strCurrentScript As String

    Dim sbScripts As StringBuilder
    ‘ If this isn’t an object that should be scripted, then just exit
    If (Me.ObjectType = Common.ObjectTypeEnum.Other) Then Return “”
    smoObjects = New Urn(0) {}
    smoObjects(0) = Me.Urn

    scScripts = scrCurrent.Script(smoObjects)
    sbScripts = New StringBuilder

    For Each strCurrentScript In scScripts

        sbScripts.Append(strCurrentScript)

        sbScripts.Append(vbCrLf & “GO” & vbCrLf & vbCrLf)

    Next

    Return sbScripts.ToString
End Function

It uses a StringBuilder object to collect the scripts (there may be more than one) for the current object, and inserts a “GO” command in between each.

Finally, in the btnGenerate_Click handler, the scripts for all selected objects are collected together, and sent to the destination (file, clipboard, etc.).

For more information about SMO, please see the following links: 

In Part 2 of “SSMS Scripter Internals”, we’ll look at the pieces of the Scripter that define it as an SSMS Plug-in.

December 15, 2006 Posted by | SQL Server, Tools and Toys | Comments Off on SSMS Scripter Internals Part 1 of 3: “A-scripting we will go”

SSMS Scripter Introduction

As part of my company’s “Do Something Cool Day”, I wrote the kernel of what would become the SSMS Scripter.  Actually, the original name of the project was “SSMS Scriptor”, but that was before I realized that I had been misspelling the word “scripter”, so unfortunately you’ll see both variations through the code.

The Scripter creates DROP and CREATE statements for Views, Stored Procedures, and User-Defined Functions in the same format as SQL Server 2000 Enterprise Manager (SSEM) did.  SQL Server 2005 Management Studio (SSMS) changed how the scripts are generated, and you can no longer get the DROP and CREATE statements in a single script  – you have to generate two different scripts, and then merge them together.

Additionally, the built-in scripting features of SSMS would add a comment line about each object scripted.  While this doesn’t affect the quality of the script functionally, it tends to make it slightly more difficult to read.

These two changes have wreaked untold disturbances in the Force, so I set out to write my own plug-in for SSMS to create the scripts in the SSEM-style, and try to set the galaxy to rights.  I unveiled the official 1.0 release of the Scripter at the December 7, 2006 Microsoft Developers of Southwest Michigan (MDSM) user’s group meeting.  Since then, I’ve extended the application to its current 2.0 version.  The latest source code can be found here: https://markegilbert.wordpress.com/ssms-scripter/ (also available via the “SSMS Scripter” tab at the top of this blog). The Scripter is currently at version 2.0, and links itself into several levels of the SSMS hierarchy for a given database:

  • Database 
  • “Views” folder 
  • Individual views 
  • “Programming/Stored Procedures” folder 
  • Individual stored procedures 
  • “Programming/Functions” folder

The Scripter allows you to specify which objects or groups of objects to script out, and then gives you three choices of destination: a specific “.SQL” file, the clipboard, or a preview window.  Among other things, my intention in describing the SSMS Scripter’s innards is to provide a source of documentation on how to build a plug-in for SSMS.  As Part 2 of my series on the Scripter explains, official documentation from Microsoft on the classes utilized for the plug-in itself are apparently still safely locked away in the minds at Redmond.

My next three blog postings will explore the Scripter’s various noteworthy aspects:

  • SQL Management Objects 
  • Building a SSMS Plug-in 
  • Opening a form with a new Thread

I hope you enjoy using this utility, and I welcome any feedback you have on its design, features, or performance. 

December 15, 2006 Posted by | SQL Server, Tools and Toys | Comments Off on SSMS Scripter Introduction