Mark Gilbert's Blog

Science and technology, served light and fluffy.

IISTweak 0.1.1 Released

I just posted a minor update to IIS Tweak.  This corrects an issue with the tree view where deleting the last item in the tree wouldn’t update the buttons or the context menu commands correctly.  You can download the compiled binary or the source at http://www.codeplex.com/IISTweak.

Advertisements

October 29, 2008 Posted by | Tools and Toys | Comments Off on IISTweak 0.1.1 Released

SSMS Scripter Updated – Major Performance Improvement

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!

October 7, 2008 Posted by | SQL Server, Tools and Toys | Comments Off on SSMS Scripter Updated – Major Performance Improvement

New Tool Published: IIS Tweak

Standard operating procedure for me and my colleagues when working on a web site locally is to take over the default web site – http://localhost.  However, we all work on multiple projects.  Switching from one to another involves opening up the IIS management console, drilling down to “Default Web Site”, opening up the properties, clicking on the Home Directory tab, browsing out to the new folder, and then clicking OK a few times to save the new settings.  I must have done this routine half a dozen times last week alone, not to mention how many times my teammates and I have done it in the past months.

Luckily, the tediousness of this process finally annoyed me to the point where I wrote a new utility to speed it up – IIS Tweak.

IISTweak

The first version, 0.1.0, uses the same treeview navigation controls that another of my utilities, NAntRunner, does.  Each node on the list has a path to a web site’s root directory tied to it.  When I double click an entry in the list, the utility will modify the IIS Metabase for web site with ID “1”, the default web site, and change the “Path” property to that path.  This magic is the result of these four lines:

Dim MyLocalhost As DirectoryEntry

MyLocalhost = New DirectoryEntry(“IIS://localhost/w3svc/” & Me._WebSiteID & “/ROOT”)
MyLocalhost.Properties(“Path”).Item(0) = value
MyLocalhost.CommitChanges()

“Me._WebSiteID” in this case happens to be a private variable initialized to “1” in the form’s constructor.  If you know the ID for another virtual directory on the server, you could substitute it here and modify that site’s Home Directory, or any of a number of other properties.  I’ve only done a little with the IIS Metabase, and the System.DirectoryServices namespace, but it looks like most of IIS (if not all) can be programmatically controlled; this is merely one simple example.

Full source code can be found on CodePlex: http://www.codeplex.com/IISTweak.  Enjoy!

September 28, 2008 Posted by | Tools and Toys | Comments Off on New Tool Published: IIS Tweak

SSMS Scripter Republished to CodePlex.com

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!

September 6, 2008 Posted by | SQL Server, Tools and Toys | Comments Off on SSMS Scripter Republished to CodePlex.com

NAntRunner 0.3 Released

I just released NAntRunner 0.3, now available at http://CodePlex.com/NAntRunner.  The biggest single change was dropping the list box of scripts, and introducing a tree view:

NAntRunner

The top level of the tree view is a list of script “groups”.  These can be called anything you like.  Within each script group you can place one or more scripts.  When you first add a script, the name that appears in the tree view will be the file name of the script itself, for example “Primary.build”.  You can rename that to anything you like.

There are controls in the tree view context menu for adding groups, adding scripts, renaming groups/scripts, deleting groups/scripts, and running scripts.  The five buttons that appear above the tree view replicate that functionality.

I’ve also added a “Last Saved” message next to the Close button in the lower right corner.  Like its predecessors, NAntRunner 0.3 automatically saves all changes (adding or removing scripts, renaming items in the tree, resizing the utility, etc.).  The message is simply there to let you know when it happens.

Upgrading to the new version is easy.  Simply download the 0.3 ZIP, and extract it to the folder with your previous installation of NAntRunner.  The 0.3 release will automatically upgrade the settings for the previous release.  Any scripts that you had defined will be placed into a single group called “Default”.  You can move them to new groups, and rename them at that point.

I had originally planned for a feature where you could drag and drop scripts to rearrange them, or move them to new groups, but I ended up postponing it until a later release.  The tree view, and specifically being able to associate a nice display name with each script, solved the main annoyance with the 0.2 release – namely that if you had scripts at any significant depth on your file system, they became unreadable.  Dropping and adding scripts is fairly easy with this release (simply copy the path from the “Path to Script” text box, hit the Add Script button, and paste it into the dialog), so I don’t see moving scripts/groups as a huge priority right now.  If that’s a feature that you would find it hard to live without, please let me know in the comments here or on the CodePlex site.

Enjoy!

August 30, 2008 Posted by | Agile, Tools and Toys | Comments Off on NAntRunner 0.3 Released

Tools and Toys Update

I’ve moved the source code for the WPF Media Player to CodePlex: http://codeplex.com/WPFMediaPlayer.  Currently the Player sits at version 0.1, but I’ve been working on a version 0.2 for a while now.  Ok, I was working on it earlier this year, and it’s been sitting dormant for a while now.  Ahem.  That should be changing in another week or so.  I’m wrapping up a personal project of mine, and then I focus on a handful of programming projects that I’ve been putting off for a while – this being one of them.

The WPF Media Player is actually the second project I’ve moved to CodePlex – the first being NAntRunner, now hosted at http://codeplex.com/nantrunner.  I have an update in mind for NAntRunner as well, which would bring it up to version 0.3.  Hopefully that will be posted in the next month or so.

Now that I’ve moved these to CodePlex, I’ve been thinking about the other items still hosted on my Tools and Toys page:

  • The SSMS Scripter seems like a natural to post there, so that one will probably be next to move.  I have an update in mind for that one as well, but it’s not clear to me how to go about making it.  I’ll have to give it some more thought first.
  • In the four months since I wrote and started using String Cleaning, I’ve found it to be eminently useful, but the core of the application is the one line of source described in this post.  I can’t really see it changing all that much, so I don’t think that’s worth setting up a new project for.  I have considered porting it to a PowerShell script (which would hopefully decrease the cold-start time), but that’s pretty low on the priority list at the moment.
  • One-Three Back Solitaire hasn’t changed in years, and it was really my learning application for Test-Driven Development.  At this point I don’t think that will move over either.  If I ever decided to add on to it (write a web-based leader board using WCF, or something like that) I’d probably move it over first, just to simplify things.

There you have it.  I’ll keep you posted on progress on these projects, and I’d love to hear your feedback on what you find useful, and what you’d like to see in future releases.

August 23, 2008 Posted by | SQL Server, Tools and Toys, WPF/Silverlight | Comments Off on Tools and Toys Update

NAntRunner 0.2 Released

I published an update to the days-old NAntRunner project on CodePlex.  I had barely begun to use version 0.1 myself and had just showed someone else how to use it, when we discovered how much it was really lacking.  Version 0.2 should address a lot of those shortcomings:

NAntRunner0_2

Natively, version 0.2 supports selecting a framework to target (pulled from the NAnt.exe.config file) as well as the build target (pulled from the script you select).  In addition, you can pass any other NAnt arguments you like using the textbox at the bottom.  The complete command executed is shown at the top of the results box, which has been moved to the right side of the screen.

Along with the updated utility, I’ve released the NUnit test suite that I’ve been compiling for the utility.  I think it is a good start, but it is not complete.  In fact if you scan through the NAntRunner source code you’ll see many TODO notes describing tests or additional validation checks needed. 

Let me know what you think of the update, and what else you’d like to see in future versions.

May 26, 2008 Posted by | Agile, Tools and Toys | Comments Off on NAntRunner 0.2 Released

NAntRunner

I’ve added a new tool to my growing collection: NAntRunner, a lightweight, standalone, GUI interface for NAnt:

NAntRunner

You can browse to multiple NAnt scripts, and they will be saved (so they will appear the next time you start NAntRunner).  Selecting one from the list and clicking Run will do just that, and display the output to the text box on the lower half of the screen.

Why did I want a GUI interface for NAnt?  Frankly, I got tired of creating Windows Explorer shortcuts that would run the script via the NAnt executable (that practice alone saved me time from having to type in the command “nant.exe -buildfile:mybuildscript.build”).  Additionally, I have half a dozen build scripts that I use currently and I’m just getting started.  Having a tool that could roll up and manage many scripts was an attractive feature.

Before I built this, though, I looked around to see if someone had beaten me to the punch.  I found a couple of plug-ins for Visual Studio (Jay Flowers‘ Studio hack and the Sharp Builder toolset) which would do basically what NAntRunner does, except from within the IDE.  Why did I want a standalone interface, separate from Visual Studio?  I am in the process of moving my company to automating our builds with CruiseControl, and NAnt is going to be a key piece to the overall solution.  Several of our projects are not .NET, and therefore not in Visual Studio, so an IDE plug-in just wouldn’t be useful or appropriate across the board.

Additionally, I wanted something that I could use to quickly test a new build script, to see if it was working the way I intended it to.  Having to fire up VS just to do this would be a hassle.  For those developers in the office that don’t have VS, it wouldn’t be possible.

The first official release, 0.1, allows you to select the NAnt executable on your machine, as well as one or more build scripts.  The only command line option for NAnt that NAntRunner supports at this point is “buildfile” – just enough to specify the script itself.  I may eventually get around to adding support for the other command line options.

The NAntRunner page on my blog has both the executable (single .exe file) and full source code for the application.  To run NAntRunner you’ll need the .NET 3.5 Framework and NAnt (of course).

Try it out and let me know what you think.  I’d also be very interested to hear what features you’d like to see in it next.

May 20, 2008 Posted by | Agile, Tools and Toys | Comments Off on NAntRunner

SSMS Scripter Update – 2.5.2

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 https://markegilbert.wordpress.com/ssms-scripter/.

February 16, 2007 Posted by | SQL Server, Tools and Toys | Comments Off on SSMS Scripter Update – 2.5.2

SSMS Scripter Update 2.5.1

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: https://markegilbert.wordpress.com/ssms-scripter/.

January 29, 2007 Posted by | SQL Server, Tools and Toys | Comments Off on SSMS Scripter Update 2.5.1