Mark Gilbert’s Blog

This minor release corrected an issue where the Scripter would register itself for the Database node in the SSMS hierarchy, or an invalid node, but not both.  This is because the context string for a Database node consists only of the server and database names.  These two properties appear in every node below the database.  The old logic would look for a view, a stored procedure, a user-defined function, or their respective folders, and if it didn’t fall into one of these six categories it would assume the user had selected the database node.  This version corrects the issue by looking for specifically valid and invalid nodes, and only when the selected node does not fall into one of these two buckets will the Scripter treat the selected node as the Database node.

The updated version of the scripter can be found at http://markegilbert.wordpress.com/ssms-scripter/.

A colleague of mine recently found that my Scripter broke with the release of SQL Server 2005 SP2, CTP.  Specifically, the Scripter would fail to add an item to the context menus for stored procedures, databases, etc..  This issue has been fixed in a new, minor release – 2.5.1.

I’ve also created a new page that contains all source code for past released versions of the Scripter, and the latest installation: http://markegilbert.wordpress.com/ssms-scripter/.

In mid-December 2006 Microsoft released SQL Server 2005 SP2 CTP (here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D2DA6579-D49C-4B25-8F8A-79D14145500D&displaylang=en#QuickInfoContainer).  While there are some nice things fixed, one thing that the CTP changes is how Management Studio plug-ins are “registered”.

All three released versions of my SSMS Scripter (available at http://www.devmi.com/meetings/2006/Pages/2006_12_07.aspx) throw an error when it tries to add an item to the content menus for Databases, Stored Procedures, etc., it throws an error to the effect of “can’t cast object of type ToolStripItem to MenuItem’.

I haven’t had a chance yet to dig into this yet, but once I do I’ll either issue an updated version, or links to articles describing this as a new bug that will be fixed when the full release of SP2 is out.  For the time being, I wanted to give everyone a heads up – the Scripter and SP2 don’t play well together.

The Scripter is now covered by the GNU General Public License.  The 2.5 release boasts improved integration with SSMS, and a few other miscellaneous enhancements:

• Carriage return issue fixed. The Scripter replaces standalone line feed characters with Carriage Return-Line Feed combinations.
• Scripter now uses the authentication used to connect to the database in SSMS, and doesn’t assume Windows Authentication.
• I added a message to the splash screen saying that it may take a while to initialize for a database with a large number of objects.
• Branches are only loaded into the TreeView when they are needed (when a branch is expanded by the user, or an object is defaulted when you first start the Scripter up), which makes for a quicker load time for the utility.
• Starting the scripter from the Database level now selects all objects in the tree.

Complete source code and the updated installer can be found at http://markegilbert.wordpress.com/ssms-scripter/.

In the previous part of this series I looked at the project settings and code components that defined the Scripter as an SSMS Plug-in.  In this third and final piece, I’ll look at the splash screen used by the Scripter, and how threading was used to make it look pretty.

Because of the way that the Scripter digs into the source database when it first boots up, databases with a large number of objects (stored procedures, views, or UDFs) can take more than a few seconds to load.  Instead of just appearing to doing nothing except churn, I added a simple splash screen that appears before the main form is instantiated.

My first splash screen had a single line of text.  As a result, it was easy to miss it on screen, since it blended in well with the half-dozen other windows that I regularly have open.  I wanted to make it stand out more, and I thought a simple animated GIF would be the best way.  I found a GIF on the web that looked similar to the moving circle icons that Microsoft seems to have incorporated into all of their most recent applications (SQL Reporting Services, IE 7, etc.).  It was large enough, and animated enough, to draw the user’s eye to the splash screen, and specifically my one line of text encouraging them to be patient (and not give in to the Dark Side of the Force).

The splash screen is opened from the Invoke method of the ScripterMenuItem class.  Originally I simply instantiated the form as a dialog, and closed it when the SSMSScriptor form finished initializing.  This caused the splash screen to open just fine, but the animation wouldn’t show because the splash screen never got a message to repaint itself.

I had first considered setting a timer to go off every quarter second or so, and force the dialog to repaint itself, but that seemed like a bit of a hack.  Instead, I opted to instantiate the form in its own thread and then kill it when the main form finished loading.

To do this, I needed to add the System.Threading namespace to my list of imports in the ScripterMenuItem class.  Then, I created three local private variables: m_tiSplashScreen, m_frmScripter, and m_frmSplashScreen.  The second one was declared WithEvents so I could declare a public ScripterMenuItem method called ScripterLoadedEventHandler that handles, well, the ScripterLoaded event of the SSMSScriptor object (a custom event).

The following is the startup sequence of events:

• The ScripterMenuItem.Invoke method creates a new thread (stored in m_tiSplashScreen), and passes it the address of a private method of ScripterMenuItem called OpenSplashScreen. This private method instantiates the local private variable m_frmSplashScreen, and opens it as a dialog. I found that starting it as a separate form caused it to open, and then immediately close back down.
• The Invoke method then starts the new thread, which causes the splash screen to actually be displayed to the user.
• Finally the Invoke method creates the new SSMSScriptor form, and opens it.
• The Load event handler of SSMSScriptor does its normal startup, and at the very end raises the ScripterLoaded event.
• The ScripterMenuItem captures the ScripterLoaded event, and kills the splash screen thread (if it is still active).

Forcing the splash screen to run using a thread separate from the main program allowed it to refresh itself and show the animation properly.

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. 

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: 

• SMO: 
  • http://msdn2.microsoft.com/en-us/library/ms162169.aspx
• Scripting with SMO: 
  • http://msdn2.microsoft.com/en-us/library/ms162153.aspx
  • http://davidhayden.com/blog/dave/archive/2006/11/09/ScriptDatabaseUsingSQLServerManagementObjects.aspx

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

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: http://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.