<< June 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

Technology Thoughts, ramblings.. etc

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

rss feed

Thursday, June 29, 2006
SMO Uses

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 Project 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 01:02 am by ausrob2003


Leave a Comment:


Homepage (optional)


Previous Entry Home Next Entry