Figure 1

You could have gotten the same information from SSMS with a fair number of mouse clicks of course, but it would not have been all summarized on a single screen. Likewise with sqlcmd you could have found the log files, but it would have a bit more typing and log file information is in different places on SQL Server 2005 than it is on SQL Server 2000; the msh commands in Figure 1 will work on either.

Msh+SMO satisfies one of my requirements for switching to a "new" technology, it takes less typing than the other technologies I have previously been using. However msh+SMO adds a few more things to the mix; it has the equivalent of Intellisense, but for the command line. It also makes it easier to integrate Windows stuff with SQL Server stuff. You certainly can use the!! syntax to invoke Windows utilities in sqlcmd scripts, but with msh+SMO it much easier to integrate the results.

Before we go any further let's talk about setting up msh+SMO. To use msh+SMO you need two things; msh and SMO. Msh is a beta product and it also requires that the .NET Framework 2.0 is already installed on your system, as does SMO. You can get msh in two ways. One is to download the standalone version of it from http://www.microsoft.com/technet/scriptcenter/topics/msh/download.mspx. The other is to download the Microsoft WinFX SDK from http://www.microsoft.com/downloads/details.aspx?FamilyID=23a22468-5807-4ff7-a363-ce6fe69b8f04&DisplayLang=en. Msh is part of the WinFX SDK.

SMO is bunch of .NET assemblies and is included in the client tools for SQL Server 2005. It gives .NET programs object models of SQL Server that they can program against. It is also available as part of the Feature Pack for SQL Server 2005 available at http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en.

At least those were the URL's as I wrote this, things do have a tendency to move around though.

Msh requires that .NET 2.0 be installed on your system, as was previously mentioned. This really a feature, not a requirement because anything that is available in a .NET assembly is available in msh. You can also extend msh with .NET assemblies to create your own shell, but I'll save that for another article. SMO is a set of .NET assemblies ergo it can be used from msh. The example in Figure 1 just used one of the object models from SMO, i.e. "$canopus4" was an object that represented an instance of SQL Server and "databases" is a property of an instance of SQL Server that contains all the databases in that instance.

Once you have installed .NET 2.0, msh, and SMO you are ready to get started. You won't be able to run the example shown in Figure 1 right out of the box because msh only knows about the assemblies that are shipped with .NET 2.0, but I'll get to that shortly. There is a lot more information about msh, also called Monad, at http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx. There is even a book from O'Reilly publishing, Monad, ISBM: 0-596-10009-4. First we will look just enough of the basics to get stated using msh+SMO, use the references mentioned for more discussion. Once you get the shell installed and start it by running the msh.exe program. You will get a console window that looks very similar to the one for the DOS shell. It is shown in Figure 2. You type command into this window.

Figure 2

Msh, as the name implies, is a shell. It wraps a hierarchical object model of, well, just about anything. The hierarchy is produced by what is called a Monad Provider and you can make your own. Msh comes with a number of providers, the obvious one is for the file system, but there are providers for the registry, environment and other things.

Figure 3 shows some very basic operations in msh. Variables can hold anything in msh, but their name always starts with "$". This example starts witha variable, named $a, being assigned j the value of one. Then another variable, named $b, being assigned k the value of $a plus one . Msh has all of the arithmetic and string operators you might expect. If you enter just the name of a variable l into msh it will write out it value.

The "|" character it the pipe operator. It says to pass whatever is on its left to what ever is on its right. We use it to pass m the $b variable to the get-member cmdlet.

In msh what you would probably call a command, msh calls a cmdlet. All cmdlets have the form of verb-noun. The get-member cmdlet tells you the type of the object passed into it and all the members of that type. Here we can see that the $b variable is of type System.Int32 and has a number of methods associated with it.

Figure 3

We can use any of those methods from msh.Figure 4 shows some of those methods being used

Figure 4

If you have been working with .NET you will probably recognize these methods, all classes in .NET have them. In some cases you might not remember what the input parameters or return types are. If you were writing code in Visual Studio you would have Intellisense to help you along. Msh has something that is sort of like "command line intellisense", you can always ask about a method of a class. Just enter the object name and the name of the method separated by a "." as shown in Figure 5.

Figure 5

This gives us our "command line intellisense". The information scrolls up on the screen rather than appearing in a tooltip, but the result is about the same. Msh is has a number of way to get help about and information out of objects. For example "get-help get*" will get you help on all the cmdlets that begin with "get".

That's a lightning introduction to msh, and is enough to go onto setting things up for using SMO. But before that we need a lightning introduction to SMO. There is a lot of reference material on SMO that is part of MSDN once you install either the SQL Server client tools or the Feature Pack for SQL Server 2005.

SMO provides a number of object models for SQL Server. One of the these is an object model of an instance of SQL Server and all the objects it contains. Typically SMO is used by applications written to manage SQL Server, for example SSMS are based on SMO.

Msh can use any of classes in a .NET assembly, including those in the assemblies distibuted with SMO, interactively or as part of a script. Figure 6 shows an example of msh making an array of strings using the .NET System.String class. The new-object cmdlet is the equivalent of "new" in C#. It is used in this example to create an instance of a class, a string array, in variable $a, that can hold three strings. Next it fills the array with the strings "a", "b", and "c", then it enters $a and msh prints out its content.

Figure 6

We see, now, that msh can make an instance of any .NET class and then use that instance in its script. The class we are interested in using is the Server class from SMO, specifically the Microsoft.SqlServer.Management.Smo.Server class. Note that there are a number of classes named Server in the .NET Framework, but they are in other namepaces.

The Server class represents an instance of SQL Server and is initialized by passing the name or IP address of the server into the constuctor for the class. That is what the example in Figure 1 did to access the default instance of SQL Server running on the CANOPUS5 machine.

In order for an application to use a class from a .NET assembly that contains the class must be loaded into that application at runtime. This is typically done automatically because a reference added to the application when it is compiled. However msh does not automatically load the assemblies needed for SMO, you must do that explicitly in msh.

There are number of assemblies associated with SMO, but for the typical things you might want to with an instance of SQL Server only three are needed. They are "Microsoft.SqlServer.ConnectionInfo" j, "Microsoft.SqlServer.Smo" k, and "Microsoft.SqlServer.SmoEnum" l. Assemblies can be loaded programmatically in .NET and Figure 7 shows the Reflection.Assembly class being used to do this. Note that even though msh does not automatically load the SMO assemblies it does automatically load most of the assemblies in the .NET frame work including the assembly that includes the Reflection.Assembly class. Also note that msh in most cases is case insensitive.

Figure 7

Once these assemblies have been loaded into msh you can start to use SMO. These assemblies were loaded into msh prior to running the example shown in Figure 1. From Figure 1 we saw a very small part of the object model for an instance of SQL Server. We saw that the instance in the $canopus5 variable contained a collection of databases. The SMO object model for an instance of SQL Server is complete and is therefore rather large, I can't even begin to go through all of it here, but we can look at a small part of it just to see how it works. Figure 8 shows a small part of the object model created by SMO for the CANOPUS5 instance of SQL Server shown in Figure 1.

The new-object cmdlet requires the full name of a class, including the namespace. It very useful to create a variable that hold the string for the "Microsoft.SqlServer.Management.Smo." namespace because it will be used quite often. That string is in the $smo variable in Figure 8.

Note that when you use msh+SMO to access SQL Server in this example the identity that you are using to run msh must have a Window login in SQL Server and you will only be able to access what the login can access. There are other ways to login to SQL Server with SMO, but I won't be covering them in this article.

Figure 8

To use an object model you have know the object model. Just trading off remembering a bunch of tables and stored procedures to use sqlcmd, for remembering a bunch of objects and their relations to each other to use msh+SMO isn't such a good deal. But wait, there' s more!?

We can use the get-member cmdlet to explore the object model. Figure 9 shows how to do this. First the $advw variable is assigned the AdventureWorks database. The instance, $canopus5 has a property named databases that is an associative array. You use the name of the database you want as the index into that array.

The object model is expanded through the properties of an object. The get-member cmdlet is used to get just the properties of the object. Note that Figure 9 only shows the first dozen or so properties.

Figure 9

Once we know an object's properties we can drill even further. Figure 10 show the $advw variable being used to get the size and whether or not the AdventureWorks database is case sensensitive.

Figure 10

So far we have seen that msh+SMO gives us "command line intellisense" and the ability to interactively drill through an object model of SQL server and even figure out what the model is on the fly. But you can also modify the instance of SQL Server. Figure 11 show and example of this. It starts by creating a new database object and assigns it to the $NewDB variable. The database isn't yet part of the instance until you call the Create method on it, which is done next. Then just to confirm it worked it uses the format-table cmdlet to list the databases in the CANOPUS5. You can see that the Sales database has been added.,

Figure 11

The last thing we will look at is functions. You can create you own functions with msh. Below is a function which is sort of like the sp_executesql stored procedure in SQL Server. This function connects to the local server then executes the command passed to it and generates tabular results for what comes back. I'm not going to go into the details of how it works, I'l leave that as an execise for you.

function executesql {

$server = new-object ('microsoft.sqlserver.management.smo.server');

write-host "executing " $args[0]

write-host "Server:"$server.Name

$rdr = $server.ConnectionContext.executeReader($args[0]);

while($rdr.Read())

{

for($c = 0; $c -lt $rdr.FieldCount; $c++) { ($rdr.GetName($c) + ":" + $rdr.GetValue($c)) }

write-host ""

}

$rdr.close();

}

Figure 12 shows using the executesql function to execute a select statement to get the rows from the authors table in the pubs database.

Figure 12

The purpose of the article has been to be a very brief introduction into msh+SMO. It is very sketchy and skips over many, many details. It showed us that with msh+SMO we have a command line tool that has its own version of intellisense and can drill though an object model of SQL Server, even if you don't know the object model before you start. But it doesn't even begin to explore what can be done with msh+SMO. If feedback on this article indicates there is some real interest in msh+SMO I'll post some more in depth articles in the future, but what's here is enough to get you started.

Dan Sulllivan

Coming in April

A Developers Guide to SQL Server 2005

Bob Beauchemin and Dan Sullivan

Addison-Wesley

ISBN: 0321382188