PROBLEM: You manage many MS SQL servers and need an easy way to review jobs that have failed.

SOLUTION: I will show you one way to accomplish this task with results displaying on the screen or to a file. To make your life easier, I recommend you download & install PowerGUI from Quest Software. This application comes with a script editor that will enhance your PowerShell development experience.

Go ahead and open a new session of PowerShell. You can type or copy and paste the code from our initial three lines of code. We will start by loading the SMO assembly & creating an instance of the server object. On the third and final line of code, we get a collection of all jobs by accessing the SQL Server agent on the server. We then pipe the resulting object and look for all jobs which are enabled that have failed.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
$srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "TUIRA"
$srv.jobserver.jobs|where-object {$_.IsEnabled -eq $TRUE -and `
    $_.LastRunOutcome -eq "Failed"
}

You will notice the result not being in a desirable format, especially when you have many jobs. We will use the information from one of the jobs to pick the specific properties we want in our report and display them in a table format. Lets pick the Parent, Category, Description, LastRunDate, LastRunOutcome, & Name properties for our next result.
On your PS session screen, hit the up key and you should get the last line of commands we executed. Hit the left arrow key to modify the command as follow

$srv.jobserver.jobs|where-object {$_.isenabled -eq $TRUE}|Format-Table `
    Parent, Category, Name, LastRunDate, LastRunOutcome, Description `
    -Autosize

Now we have a result we can save to a file or print. But before doing that, we’ll expand this script to retrieve the jobs from multiples servers. Lets go ahead and open a Notepad session and enter the name of a few MS SQL servers you manage, one name per line, and save it with the file name serverlist.txt. I saved mine in C: for simplicity. Now, we just need to enclose our previously executed commands in a loop.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null
foreach($svr in get-content C:serverlist_prd.txt) {
    $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
    $srv.jobserver.jobs|where-object {$_.isenabled -eq $TRUE}| `
        Format-Table Parent, Category, Name, LastRunDate, `
        LastRunOutcome, Description -Autosize
}

I find this looping syntax very helpful whenever I need to execute code against multiple servers. The result from this modified version of the original code will leave a blank space between each server or iteration of the code. We’ll solve that with a variable in the next and final version of the script which includes the code to save the report to a file.

CLEAR
Set-Content -Path "C:TempMonitorJobs_LowerEnvironments.txt" -Value " "
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|out-null

foreach($svr in get-content C:tempserverlist.txt) {
    $srv=New-Object "Microsoft.SqlServer.Management.Smo.Server" "$svr"
    $a += $srv.jobserver.jobs
}

$a|where-object {$_.isenabled -eq $TRUE -and $_.LastRunOutcome `
    -eq "Failed"}|Sort-Object -Descending LastRunDate| `
    Format-Table Parent, Category, Name, LastRunDate, `
    LastRunOutcome, Description -Autosize|Out-File `
    -FilePath C:TempMonitorJobs.txt -Append -Encoding ASCII

PROBLEM: I need an easy way to script out all the tables from a database in MS SQL 2000 and above.

SOLUTION: Like anything in programming, there are a thousand ways to skin a cat. Here, I’ll show you the simplest way I found to meet this request.

Go ahead and open a new session of PowerShell. You can type or copy and paste in your PS session window all the code in this solution. We will start by loading the SMO assembly.

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")|out-null

Next, we’ll create an instance of SMO server object, database object, and scripter object

$srv = new-object "Microsoft.SqlServer.Management.SMO.Server" "."
$db = new-object("Microsoft.SqlServer.Management.SMO.Database")
$scr = new-object("Microsoft.SqlServer.Management.SMO.Scripter")

We then assign the instance of MS SQL Server we want the scripter to use. We wrap it up by calling the Script() method with an SQL SMO collection of objects to be script out –in this case, all tables.

$scr.Server = $srv
$scr.Script($srv.Databases["AdventureWorks"].tables)

That’s it! The simplest way to script out all tables from a database with PowerShell. In a future article, I’ll expand from this solution by writing a script that will take parameters, use SMO Scripter Options, filter tables to be script out, and then some.