Checking Error table with Powershell

One of our main web applications writes audit and error messages to the SQL database.  I was looking for a faster way to get a quick overview of the last few messages written to the table.  I could save a sql script, but seriously, where's the fun in that?  And besides, it would require me opening up SQL Query Analyzer. However, I do keep a Powershell window open at all times. 

Speaking of Query Analyzer, I had a significant breakthrough to a new level of keyboard zen with it last week.  More on that later. 

Onto the script:

   1:  $count = 50

   2:  if ($args.length -gt 0) { $count = $args[0] }

   3:   

   4:  $conn = new-Object System.Data.SqlClient.SqlConnection

   5:  $conn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDatabase;Data Source=MySqlServer"

   6:  $conn.Open()

   7:   

   8:  $cmd = new-Object System.Data.SqlClient.SqlCommand

   9:  $cmd.CommandText = "SELECT TOP $count * FROM tblError ORDER BY ErrorID DESC"

  10:  $cmd.Connection = $conn

  11:   

  12:  $da = new-Object System.Data.SqlClient.SqlDataAdapter($cmd)

  13:  $ds = new-Object System.Data.DataSet "Errors"

  14:  $da.Fill($ds) | out-Null

  15:  $conn.Close()

  16:   

  17:  $ds.Tables[0].Rows | Format-Table -property ErrorID, errorTime, ErrorMessage -wrap -autosize

  18:   

  19:  rv count, conn, cmd, da

The first thing to do is to create a count variable that will be used to determine how many records to pull from the db.  It defaults to 50, but that can be overwritten in line 2 if you pass in a parameter to the script.  Lines 4-15, should look familiar.  They basically use ADO.NET objects to get the records from the db and place them in a DataSet.  Line 17 is responsible for displaying the results in the console.  I use the Format-Table cmdlet to filter out some of the fields and to make them look pretty by autsizing them and causing the cells to wrap their text.

In line 19, a bunch of variables are removed.  Normally, you wouldn't have to worry with this step as the variables will go out of scope as soon as the script finishes.  I did this, incase I ran the script in global scope.  You can accomplish this by inserting a ". " at the beginning of the command line.  In which case all of the variables would remain even after the script is complete.  In my case, I'm removing all the variables except the dataset.  That way, I can continue to manipulate the object and if I want to refresh it, I just re-run the script.