<< May 2006 >>
Sun Mon Tue Wed Thu Fri Sat
 01 02 03 04 05 06
07 08 09 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

Technology Thoughts, ramblings.. etc

If you want to be updated on this weblog Enter your email here:

rss feed

Monday, May 22, 2006
SMO Uses and more

My Eventual Next Blog Update

A Word from our sponsors: Vista Support Tool

See if your machine is capable of running the next Microsoft platform:
Windows Vista.  The following is a link to a beta utility which will test
your computer's capabilities with a view to supporting Vista's core functionality:


Microsoft Australia: Tech Ed 2006

Check it out.. Tech Ed is on, and the site's up:


Main Feature: Using SMO to generate stored proc script - by your design.

First thing to do: Upgrade to SQL Server 2005.  Install SP1 and then start a new C# (2.0) project.

It's so simple to create SQL Server utilities from .Net nowadays, even more so than wit SMO's
predecessor, DMO.  I've written half a dozen little utilities to help with day-to-day operations,
like preparing a script containing 24 hours worth of updates to Stored Procedures.

Here's how to batch write in T-SQL the syntax of updated Stored Procs.. Read along..

You've created a C# console app, say.  Add the appropriate (minimal) .NET References (Microsoft.SqlServer.Smo, Microsoft.SqlServer.SmoEnum and Microsoft.SqlServer.ConnectionInfo).

In code, add the appropriate using directives as so:
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Generic;

Now, connecting and so forth is so easy (depending on your setup).  You can simply do this:

Server _Server;
Database _Database;

string server = "server";
_Server = new Server(server);

string db = "database";
_Database = _Server.Databases[db];

This sets up the objects you need - including authentication.
You may need additional authentication, but that is outside the
scope of this post.

Now, lets get to work..

Using generics, create storage for all Stored Procs we
want to include

List<SqlSmoObject> procedureList = new List<SqlSmoObject>();

foreach (StoredProcedure sp in _Database.StoredProcedures)
 if (!sp.IsSystemObject)
         if (WasCreated(sp))

The WasCreated() function is examined in detail below.

Now, create the Scripter object

Scripter spr = new Scripter(_Server);

//Write the Create Statements
spr.Options.Permissions = true;
spr.Options.ScriptDrops = false;
spr.Options.FileName = "path and filename";

Once the options are set, we can create the
script.  Notice that the Scripter will write out
the output file as an option

str = spr.Script(procedureList.ToArray());

Here is the content of the WasCreated -- used
to determine the age of the Proc.  Note:
_ageComparison could be anything, though
I am using it in the context of days (1 or more)

static bool WasCreated(StoredProcedure sp)
 TimeSpan ts = DateTime.Now - sp.CreateDate;

        //Less than a day old
        //if (ts.Days < 1)
        if (ts.Days < _agecomparison)
         return true;
        return false;

It's that easy..

-- Now for something completely different

The Ideas Dept brings you:

Great Ideas for Windows Shell

- Instead of loading 1,000+ files all at once (inconveniently)
  and blocking the UI with the "searchlight" animation, why not load the first, say, 200
  files then process the rest of the files as a background thread?

  - or better -

  Why not load the first 50 files, then only process the rest of the folder if the user
  interacts with the folder or its' contents?


Posted at 04:34 pm by ausrob2003


Leave a Comment:


Homepage (optional)


Previous Entry Home Next Entry