Friday, September 5, 2008

Scripting with SQL Server Management Objects

When I needed to move about 28500 stored procedures from a SQL Server 2000 to a SQL Server 2005, I turned to the SQL Server Management Studio. It didn't offer any option to move the stored procedures but it did allow to script them to a text file. I launched the "Generate Scripts" task and noticed that after 8 hours of processing only 3500 stored procedures had been scripted. The process had also eaten all the RAM on my workstation.

It would takes ages at this rate so I cancelled the task and turned to the SQL Server Management Objects that are installed together with SQL Server Management Studio. According to MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

The following code shows the C# application that I wrote to quickly generate scripts for all stored procedures in one database. In order to make it compile, you will need to add references to these assemblies:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.ConnectionInfo
What the application does in general is:
  • connect to the database
  • loops over the collection of all stored procedures
  • script it to a text file if it is not a system stored procedure

using System;
using System.IO;
using System.Text;
using System.Collections.Specialized;

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace SProcScripter
{
  class Program
  {
    static void Main(string[] args)
    {
        int success = 0;
        int failure = 0;

        StreamWriter sw = new StreamWriter("d:\\SProcs.sql");

        // Connect to server and database
        Server srv = new Server("myServerName");
        Database db = srv.Databases["myDatabaseName"];
        Scripter scrp = new Scripter(srv);

        scrp.Options.ScriptDrops = false;
        scrp.Options.WithDependencies = false;

        foreach (StoredProcedure sp in db.StoredProcedures)
        {
            try
            {
                Urn[] smoObjects;
                smoObjects = new Urn[1];
                smoObjects[0] = sp.Urn;

                if (!sp.IsSystemObject)
                {
                    Console.Write("Scripting " + sp.Name);

                    StringCollection sc = scrp.Script(smoObjects);
                    StringBuilder sb = new StringBuilder();

                    foreach (String st in sc)
                    {
                        sb.AppendLine(st);
                        sb.AppendLine("GO");
                    }

                    sw.WriteLine(sb.ToString());

                    success++;
                    Console.WriteLine(" done.");
                }
            }
            catch (Exception ex)
            {
                failure++;
                Console.WriteLine(" failed with exception: " + ex.Message);
            } // try - catch
        } // foreach stored procedure

        sw.Close();

        Console.WriteLine("Success: " + success.ToString());
        Console.WriteLine("Failure: " + failure.ToString());
        Console.WriteLine("Press any key");
        Console.ReadKey();

    } // void Main
  }
}

All stored procedures were generated within 30 minutes! I've tried to make this work faster using a ThreadPool but the SMO components are not thread-safe and all sort of unexpected errors and race conditions popped up. I needed to introduce several locks to keep the object out of each others way. I succeeded in making it work but due to these locks it wasn't really faster than the single threaded version above.

Thursday, September 4, 2008

Stand-Alone DTS Designer on SQL Server 2005

This article describes how to open the DTS Designer for SQL Server 2005 programmatically. It eliminates the need to open the SQL Server Management Studio if you just need to edit a DTS that was saved as a structured storage file. I’m building a command line utility in C# that takes the full path to a dts package as a parameter and opens it in the designer. You can find its VB.NET code here.

In my previous post, I already created a similar utility in VB.NET that opened the DTS Designer on PCs with SQL Server Enterprise Manager installed, which is the client application for SQL Server 2000. The source code is exactly the same. The only differences are that you'll need to install the DTS Designer Components from the Feature Pack for Microsoft SQL Server 2005 and that your project will reference the Microsoft.SQLServer.Dts80.dll assembly from the GAC

Visual Studio 2005 doesn't allow you to reference this assembly directly from the GAC. If you would browse to the GAC's folder (by default in the %windir%\assembly folder), the Assembly Cache Viewer (SHFusion.dll) shell extension will be actived which doesn't have an option to copy assemblies. You could disable this shell extension but it is just as easy to copy the assembly to a diffent folder using the command prompt:

  1. Click Start
  2. Click Run...
  3. Type 'cmd.exe' and press <enter>. The command prompt appears
  4. Type the following command:
copy %windir%\assembly\GAC_MSIL\Microsoft.SqlServer.Dts80\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.Dts80.dll c:\data\

This will put a copy of the Microsoft.SqlServer.Dts80.dll to c:\data\ from where you can reference it in Visual Studio 2005. As soon as the project is finished and compiles you don't need this file anymore as your executable will use the one in the GAC.

In order to get started, open Visual Studio and create a new Windows application in C#. Put a reference to the Microsoft.SqlServer.Dts80.dll that you have copied to c:\data\

Create a ShowPkgDesigner function that takes the full pathname of an existing DTS package:

private static void ShowPkgDesigner(string sPackagePathName)
{
  DTS.CDTSLegacyDesigner designer = null;
  String packageName = String.Empty;
  String packageID = String.Empty;
  String packageVersionID = String.Empty;

  //* Remove quotes from packagePath
  sPackagePathName = sPackagePathName.Replace("\"", "");

  if (sPackagePathName == String.Empty ||
      ShowPkgBrowser(ref packageName, ref packageID,
                     ref packageVersionID, sPackagePathName))
{
  try
  {
    designer = new DTS.CDTSLegacyDesigner();

    designer.Initialize();
    designer.Location = 2;
    designer.PackageID = packageID;
    designer.PackageName = packageName;
    designer.ServerName = sPackagePathName;
    designer.VersionID = packageVersionID;

    //* Launch designer
    designer.ShowDesigner();
  }
  finally
  {
    if (designer != null)
    {
      designer.Dispose();
      designer = null;
    }
  }
}
}

Multiple DTS packages and versions can be safed in one structured storage file. The ShowPckBrowser function allows a user to select a specific version of a DTS package:

private static bool ShowPkgBrowser(ref String sPackageName,
                                  ref String sPackageID,
                                  ref String sPackageVersionID,
                                  String sPackagePathName)
{
 DTS.Package package = null;
 DTS.SavedPackageInfos packageInfoColl = null;
 DTS.SavedPackageInfo packageInfo = null;

 int selectedPackage = 0;

 package = new DTS.Package();
 packageInfoColl = package.GetSavedPackageInfos(sPackagePathName);

 if (packageInfoColl.Count == 1)
   selectedPackage = 1;
 else
 {
   PackageBrowser pckBrowser = new PackageBrowser();
  
   for (int i = 1; i <= packageInfoColl.Count; i++)
   {
     packageInfo = packageInfoColl.Item(i);
     pckBrowser.AddPackageVersion(
     packageInfo.VersionSaveDate.ToString(),
     packageInfo.PackageName);
   }
  
   if (pckBrowser.ShowDialog() == DialogResult.OK)
     selectedPackage = pckBrowser.SelectedPackage;
 }
  
 if (selectedPackage > 0)
 {
   packageInfo = packageInfoColl.Item(selectedPackage);
   sPackageName = packageInfo.PackageName;
   sPackageID = packageInfo.PackageID;
   sPackageVersionID = packageInfo.VersionID;
  
   return true;
 }
 else
 {
   return false;
 }
}

The code above uses a custom form that only contains a listbox. In the for loop, all versions and package names are added to that listbox. As soon as a user double-clicks on the desired version, the 'PackageBrowser.SelectedPackage' contains the index of that version. This is then used to retrieve the package name, id and versionId. I'll leave it up to the reader to implement the PackageBrowser component.

All that's left to do is to call the ShowPkgDesigner function from the main function. I want my executable to take the full path to the DTS package so that I can link it to "*.dts" files in Windows Explorers' "Open With..." functionality:

static int Main(string[] args)
{
  try
  {
    String packagePath = String.Empty;
    
    // Full path to the DTS package is the first command line option
    if (args.Length > 0)
    {
      if ((new System.IO.FileInfo(args[0])).Exists)
        packagePath = args[0];
      else
      {
        OpenFileDialog openFiledlg = new OpenFileDialog();
        
        openFiledlg.CheckFileExists = true;
        openFiledlg.Filter =
        "DTS Packages(*.dts)*.dtsAll Files(*.*)*.*";
        
        if (openFiledlg.ShowDialog() == DialogResult.OK)
        packagePath = openFiledlg.FileName;
      }
    }
    
    ShowPkgDesigner(packagePath);
    
    return 0;
  }
  
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message, "DTSDesigner90 Error",
    MessageBoxButtons.OK, MessageBoxIcon.Error);
    return -1;
  }
}

If no path is provided to the command line, an empty designer sheet is opened. If an invalid pathname is passed, the user will be prompted to browse to an existing file.

Having this command line utility has really improved my productivity when I was constantly editing and comparing DTS packages that were scattered all over the companies network. Being able to open a package by just double-clicking it is just that little bit more efficient than opening them through the SQL Server Management Studio.