Hey, Scripting Guy! One thing I do not understand is why the SQL Server team wrote its own version of Windows PowerShell. I thought Windows PowerShell was supposed to standardize things. But the SQL Server team has one version of Windows PowerShell, the Exchange team has another, and then of course there is the Windows PowerShell that we can download. What gives?

– MP

Hi MP,

It has warmed back up here in Charlotte, North Carolina, in the United States. This does not stop Ed from having a cup of hot tea. This afternoon, it is a nice Earl Grey, which is probably his favorite tea. He found a place online where he could order some ANZAC biscuits, and he is listening to some Chopin on his Zune while going through the e-mail sent to scripter@microsoft.com. (Scripting Editor: If you ever want to see Chopin’s grave, it’s in Pere Lachaise Cemetery in Paris, the same cemetery that is “home” to Jim Morrison, Gertrude Stein, and countless other famous folks.)

You know, MP, your question is a common one, and yet is one that cannot be answered. This is because it is based upon a misunderstanding. There is only one Windows PowerShell. The reason the Exchange Windows PowerShell tool seems different is because they created a custom Windows PowerShell profile that loads the Exchange snap-in and creates a couple of special functions. This is designed to make it easier for you to use Windows PowerShell to manage Exchange 2007.

The version of Windows PowerShell that is used by the SQL team in SQL 2008 is the same version of Windows PowerShell that is included on the disk with Windows Server 2008—it is Windows PowerShell 1.0. The reason some commands are missing and other commands are added is because the SQL team created what is called a “mini-shell.” They did this to…well, wait, let’s not spoil Buck Woody’s article today.

Because Ed does not consider himself to be a SQL Server 2008 guru, we decided to pass this question off to Buck Woody, SQL Server Technical Specialist for the Microsoft Corporation. He is a former Program Manager on the SQL Server team and the author of seven books about SQL Server. (Compared to Buck, Ed cannot even spell SQL. Oh, wait, he just did.)

On Tuesday and Wednesday of this week, we explained that Windows PowerShell is a shell, a set of new commands, and a way to script tasks. We showed you how to load the Server Management Objects (SMO) library—the same code that SQL Server Management Studio uses—to find information about database objects, and even how to perform actions on them such as setting properties or scripting them out to a file.

But moving through the SMO library can be, well, somewhat difficult. There are objects, collections, and so on, and then there is the complete model itself to memorize. Memorizing a Microsoft Object Model is fun (or is that “fun”); it is just that you do not have to memorize it if you do not want to do so. Just the other day we were having a small trivia contest in the office about the EnumCandidateKeys() function. But we digress.

What would really be useful is for Microsoft to hide most of this model complexity by exposing the objects database administrators (DBAs) already know about, such as databases, tables, and logins, in a more obvious way. DBAs need something that they are already familiar with that they can easily locate. You know, such as drive letters. And they need to be able to tie that back to SQL Server concepts.

So we did.

The SQL Server team created a set of mappings for the SMO object model so that they can be treated exactly like a drive letter. In other words, instead of having to set a variable to the instance and database objects that are displayed, you can just use the cd (change directory) shortcut to move around. Basically, you are dropped right into a SQL Server Instance, moving around the hierarchy of objects by using a simple cd command.

The way the SQL Server team performed this feat of derring-do is by writing a provider that maps the objects to a hierarchy. Windows PowerShell is very comfortable with providers. In fact, even the drive letters we have been using in all our examples are from a storage system provider. And there are other features. You can browse the registry (HKLM=Local Machine and HKCU=Current User) and even variables as drive letters! You can see which ones are loaded in your Windows PowerShell environment by opening Windows PowerShell and typing the Get-PSProviders cmdlet:

Get-PSProviders

What can you do with these providers? Well, that all depends on how they were implemented by the team that wrote them. The Scripting Guys use the registry provider all the time to see whether SQL Server is installed:

DIR “HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server”

Seems as if SQL Server 2008 (100) and SQL Server 2005 (90) are installed on this system. Better make a note. Anyway, as we were saying, the SQL Server team made a provider for SQL Server 2008. But they went somewhat further.

Not only did the SQL Server team make a provider for Windows PowerShell (which is really just lots of code), but they took a snapshot of Windows PowerShell and bundled all of it together. The result is another copy of Windows PowerShell, which is named sqlps.exe. We have been using the “regular” Windows PowerShell .exe file to this point. However, if you have SQL Server 2008 client tools installed, you can just close the regular Windows PowerShell window and open the SQL Server version by typing sqlps.exe at the command line. Or, even better, you can right-click almost any object in SQL Server Management Studio (SSMS) and then click Start PowerShell.

If you start Windows PowerShell by using SSMS, type this command (you are already in the right place if you typed sqlps.exe):

CD \

You are now positioned in the outmost container of the SQL Server provider, which is named sqlserver. And what is in here? Let us see:

DIR

You receive four items back:

Let us move around a bit and see how this works. As we move through the various objects, you will start to appreciate the difference between declaring and using your own SMO objects and being able to walk through the instance and database objects.

We will start with the SQL directory:

CD SQLSERVER:SQL

As soon as you are there, you can type dir to see the name of the server upon which you are working. From there, type cd and the name you see. Something like this:

CD MyServerName

By the way, you can type cd and then a few letters of the name and then press TAB. Windows PowerShell will complete everything for you. This works anywhere. The formal name for it is “tab expansion,” and it has nothing to do with how you feel after finishing a diet soda.

Now that you are inside, you can type dir again, and this time you are presented with the names of the instances installed on this server. Type cd and then the name of a running instance (or use that TAB trick) to enter the instance.Now type dir and press ENTER. Here is the list of objects that you will get:

Let us drive around in here a bit. You should still be in the name of the server, which as you have seen is the name of the instance. Let us jump in to the databases object:

CD Databases

Do remember tab expansion trick of typing some letters and pressing the TAB key until you get what you want. It will save you time and errors. Trust us, we know!

You can type dir and press ENTER to see your databases. Let us assume that you have the AdventureWorks2008 sample database installed for these examples. If not, the AdventureWorks, Northwinds, Pubs, or even a test database that you make will work just fine. In any case, type cd and the name of that database, and then press ENTER.

By the way, you might encounter situations where the name of an object has a character in it that Windows PowerShell uses for something else, such as the backslash (\). You have several options for using that object’s name. The first way is to use some letters and the TAB key as we have been telling you. The second way is to use the standard escape characters in Windows PowerShell. You can also just ask the SQL Server PowerShell provider. You can just use one of the SQL Server provider cmdlets that we will see more of in a moment called Encode-SqlName. Just follow that cmdlet with the name of a SQL Server object in quotation marks, and it will tell you what to type:

encode-SqlName “Production.Product”

Now browse to the tables object and then into a table in your sample database. You can see that even a single table is a container. It contains columns, indexes, triggers, and so on. We will use those as we go along.

Although you can do some unbelievable things with the Windows PowerShell provider for SQL Server 2008 by moving through it and using various standard Windows PowerShell cmdlets, the SQL Server team threw in some extra commands to help you with some common tasks. As with most Windows PowerShell commands, you can use the Get-Help cmdlet to find more:

get-Help encode-SqlName

Let us take a closer look at several of these. You have seen one SQL Server 2008 provider cmdlet already—Encode-SqlName. There is a corresponding cmdlet called Decode-SqlName. It just takes the strange characters from the previous command and returns the friendly name.

Many DBAs have many Transact-SQL scripts that they have created over the years. They run those scripts either in SQL Server Management Studio in a query window, or they run it using sqlcmd.exe. One of the first cmdlets the SQL Server team wrote was Invoke-Sqlcmd. This cmdlet works exactly as sqlcmd.exe does, but because you are inside the security context of an instance, you do not have to supply any other credentials.

You can use Get-Help to learn more, but let us run a simple query against the AdventureWorks2008 database by using the Invoke-Sqlcmd cmdlet:

Invoke-Sqlcmd -Query “SELECT @@VERSION;”

By the way, if you execute this command while you are in your instance directory, you will receive a warning that you are using the current context. If you want to run the query just at sqlserver: or against another server, just add the –ServerInstance parameter:

invoke-Sqlcmd -Query “SELECT @@VERSION;” -ServerInstance “MyComputer\MyInstance”

There are several other parameters that you can use. To see those, just type Get-HelpInvoke-Sqlcmd. You can use Invoke-Sqlcmd with input files. Uou can output the results to a text file (as we explained a few articles back) and more.

Here is a list of the SQL Server provider cmdlets and how they are normally utilized:

That’s it for this time. We will put all this power to work tomorrow.

Thank you, Buck, for another interesting article. And great question, MP. Join us tomorrow as we bring Windows PowerShell with SQL Server 2008 Week to its exciting conclusion. Until then, be careful.

Ed Wilson and Craig Liebendorfer, Scripting Guys