SQL 2008 Agent Jobs - Tokens work in PowerShell!

on December 2, 2009

I have been working away building out servers in our new prod test environment, and automating as much as possible along the way with PowerShell. I have to say that it’s been really fun and PoSH has brought back that loving feeling that I always had for Perl. If a programming language can be friendly, PowerShell manages it.

One thing I had the chance to test out yesterday was using SQL Agent tokens in PowerShell type SQL Agent steps. I am very happy to find that the tokens work just like I was hoping they would. In other words, this works:

$instanceName = &amp;quot;$(ESCAPE_SQUOTE(SRVR))&amp;quot;
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

The SRVR token will return the connection name of the sql server with the instance name. This works on standalone and named instances, and for clustered instances you get netname\instancename. So this is perfect for creating a sql server object and connecting to it.

Why would you want to do this? Why not just use TSQL?

The primary reason I currently have to use a PoSH step is to execute commands against the operating system. In this case, I needed to connect to my sql instance and get some information about it to then use in the OS level commands I needed to run. I have a specific example of this I’ll post in the coming days.

How Do I Test This Out?

If you’re new to PowerShell, find yourself a test box to play around on. It needs to have PowerShell and SQL 2005 or 2008 (the client tools/SSMS are enough, they provide the SMO assemblies). Open a PowerShell command prompt as administrator. To get yourself connected to a SQL Server Instance, it’s as simple as this:

#This loads up the SMO module which will be used to connect to SQL
[System.Reflection.Assembly]::LoadWithPartialName(&amp;quot;Microsoft.SqlServer.SMO&amp;quot;) | out-null

$instanceName = 'NETNAME\INSTANCE'; # or just &amp;quot;COMPUTERNAME&amp;quot; for the default instance

#this passes in the name you used above to create a new object
$srv=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $instanceName

#this will show you a table of all the properties of your SQL Server.
$srv.Properties | format-table -auto

#this will show you a table of all the properties of tempdb on your SQL Server.
$srv.Databases['tempdb'].Properties | format-table -auto