Mark Gilbert’s Blog

I just released a new version of the SSMS Scripter – version 2.6.0 is now available at http://CodePlex.com/SSMSScripter.

The major update for this version was a huge (two-orders-of-magnitude-huge) improvement in the time needed for the Scripter to load up once invoked from SQL Server Management Studio.  With version 2.5.3, even databases with a handful of stored procedures, views, or user-defined functions would take 100-200 seconds for the Scripter to spin up.  It ended up being three lines of code to drop that load time to two seconds or less, even for large databases.

After doing some digging, I found this article by Bill Graziano dated November 29, 2005 (just about a year before I wrote the very first version of the Scripter): http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated.  This article was doing the same basic thing that I was – enumerating the non-system objects in a database via SMO.  Bill’s sample code was referencing the same property I am – IsSystemObject – and found it to be very slow.  Boy, this sounds familiar.

The reason for the lousy performance was explained by a member of the SQL Server Product Team, Euan Garden, who was responding to Bill’s post (a link to Euan’s comments is included in Bill’s article).  By default, the IsSystemObject property is not returned when you get a collection of objects back (like stored procedures).  As Euan explains, this was done to optimize how much SMO needs to retrieve with each request.  As it turns out, you can force specific properties to be returned, though:

Me._CurrentServer.SetDefaultInitFields(GetType(StoredProcedure), “IsSystemObject”)
Me._CurrentServer.SetDefaultInitFields(GetType(View), “IsSystemObject”)
Me._CurrentServer.SetDefaultInitFields(GetType(UserDefinedFunction), “IsSystemObject”)

I added these lines to the newly created (as the result of a refactoring) “CurrentServer” property in SSMSScriptor.vb.  This configures the SMO.Database object to bring IsSystemObject back as one of the properties of the StoredProcedure, View, and UserDefinedFunction objects.  By not doing this, the code was having to make a round trip to the database for EVERY object in the collection before it could say whether or not it was a system object.  This meant every stored procedure in the database, plus all of the ones in Master – thousands in all.  And it ONLY took 200 seconds.  Ahem.

In addition to the performance boost, I also updated the class headers throughout to reflect the license that the Scripter is being released under now, refactored the main interface class a bit, modified the interface so that it no longer closes once you finish scripting to a file or to the clipboard, and adjusted how the progress bar/status messages are displayed.

Enjoy!

I’ve moved the source code and latest release (2.5.2) to CodePlex: http://CodePlex.com/SSMSScripter.  This particular version was the last one released, and was done so here on my blog in February 2007.

When I first released it in late 2006 and early 2007, I wrote a series describing how the plug-in worked: http://Mark-Gilbert.com/category/sql-server/.  In the “Part 2″ post in that series, I mentioned that I couldn’t find any decent documentation on how to write an SSMS plug-in.  At the time I had hoped that my series would help form that documentation.  Since then I’ve found another post at JCooney.net that goes into some additional depth from my series: http://jcooney.net/archive/2007/11/26/55358.aspx.  As the link implies, Joseph Cooney wrote this in late 2007, almost a year after I wrote mine and he had a similar lament with a section of the post titled “So Why Does This Qualify as a Black Art?”.  I agree with Joseph – writing one of these is definitely a black art, and I had to play a lot of code-roulette to get my plug-in to work.

For the next release, I have a performance-related update in mind, but I’m still sorting out how I might go about it.  The actual process of generating the scripts is relatively quick (measured in seconds), regardless of the number of objects being scripted.  The Scripter startup time can be substantial, especially for databases with large numbers of views/stored procedures/functions, and especially if you invoke the utility at the Database level in the SSMS hierarchy.

I also have not tried installing it with SQL Server 2008, and I’m eager to see how well (or not so well) it works there.

Let me know what else you’d like to see in the next release!

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.