THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Reinventing the Wheel – Automating Data Consistency Checks with Powershell

When I started in my current position at the beginning of the year, one of the first things that I did was to schedule a sit down with the various teams of Analysts that exist in our organization to find out more about their systems.  One thing I am always interested in is the manual processes that people do routinely that might be able to be automated.   A couple of the analyst mentioned that they routinely run queries in their systems to identify issues so that they can proactively fix them before they become an actual problem.  My first response was, this sounds like a good candidate for Reporting Services Subscriptions, so I asked for a sample of the queries that they were running.  What I got was a couple of emails with dozens of TSQL Script files attached to them, as well as comments that as new issues arise, new scripts are created to identify those problems.

I fully intended on creating Reports in Reporting Services for each of the scripts, but the time has never presented itself.  One of the requirements was for the Reports to only generate if data actually was returned by the script.  I asked on Twitter if this was possible using data driven subscriptions and was told that it was, but not being a SSRS person, this ended up on the back burner waiting on time to learn how exactly to go about creating the appropriate data driven report to accomplish the goal.  I had originally planned to create one or two of the reports and then go back and teach the analysts how to create the reports on a development Reports Server themselves.  Once they had been tested, I would them migrate them to the production server and setup the appropriate subscriptions for them to automate the process.

Fast forward four months, and this project has never even gotten off the ground.  While it was important to me, and important to the analysts, other more important tasks always relegated this to the back burner.  Last week when one of the analysts asked me about this again, and sent along a new batch of scripts that needed to be included, I got the idea to attack the problem from a different angle.  I’ve been using PowerShell a good bit lately to pull information from SQL Server, so why not leverage what I know and tackle this problem in a easily scalable manner?

I know that this is completely reinventing the wheel, but it actually took less time to create a solution to this problem using PowerShell than it took to write this blog post.  I provided the analysts a sample TSQL file that had a commented header section in it that contained information that would be used by a PowerShell script to control execution of the scripts similar to the following:

*                                                                                           *
*        Name:           <Script Name>                                                      *
*        EmailTo:        <Email Address List>                                               *
*        Description:    <Description for the Email>                                        *
*        HowToFix:       <Information on how to fix the problem>                            *   
*        Server:         <SQL Server Name>                                                  *
*        Database:       <Database Name>                                                    *
*                                                                                           *

-- SELECT Statement that should return no results

The header information is parsed using simple regular expressions that look for the header information followed by an asterisk, capturing the information contained for each of the required pieces of information.  Then a connection is made to the SQL Server and Database using SMO and the contents of the TSQL query file are executed.  If a result set is returned, the script formats that information into an HTML Table using the ConvertTo-Html commandlet.  This was the first place I ran into a problem, because the HTML output included five additional columns (RowError, RowState, HasErrors, Name, Table, ItemArray) that are properties of the .NET DataRow object.  Some help from Twitter by Doug Finke (Blog|Twitter) showed me that by using the –ExcludeProperty option I could drop these columns from the output HTML table.  for pretty formatting, a -Style in CSS form is applied in the ConvertTo-Html commandlet, and the Description and HowToFix sections of the header are added as the –Body of the resulting HTML document.  Then a email is sent through an internal SMTP relay to the address(es) specified in the header information.

The PowerShell Script only requires one input parameter $ScriptPath, which is a UNC share that has all the scripts in it, allowing the analysts to update and add scripts as needed to the mix of items that are being monitored.  A parameter was used to allow for segregation between groups for the script files and to better organize the scripts into logical groups.  The PowerShell script for this process is:

param(    [Parameter(Mandatory=$TRUE)]
        [string]$ScriptPath=$(Throw `
            "Parameter missing: -ScriptPath Path"))

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

[string]$style = "<style type='text/css'>
    TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;font-size:12px;}
    TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:#D3D3D3 }
    TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:#FFFFFF }

[string]$Body = "<p><b>Description:</b></p>
<p><b>How to Fix:</b></p>

Get-ChildItem $ScriptPath -Filter "*.sql" | % {
    [string]$sqlcmd = ([system.string]::join("`r`n", (Get-Content $_.FullName)))
    [string]$ScriptName = [regex]::Match($sqlcmd, "ScriptName:\t(.*?)\*").Groups[1].Value.Trim()
    [string]$Server = [regex]::Match($sqlcmd, "Server:\t(.*?)\*").Groups[1].Value.Trim()
    [string]$Database = [regex]::Match($sqlcmd, "Database:\t(.*?)\*").Groups[1].Value.Trim()
    [string]$Description = [regex]::Match($sqlcmd, "Description:\t(.*?)\*").Groups[1].Value.Trim()
    [string]$HowToFix = [regex]::Match($sqlcmd, "HowToFix:\t(.*?)\*").Groups[1].Value.Trim()
    [string]$EmailTo = [regex]::Match($sqlcmd, "EmailTo:\t(.*?)\*").Groups[1].Value.Trim()

    $smosvr = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server
    $smodb = $smosvr.Databases[$Database];
    $result = $smodb.ExecuteWithResults($sqlcmd)

    if ($result.Tables[0].Rows.Count -ne 0)
        [string]$emailbody = $result.Tables[0].Rows | `
            select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | `
            ConvertTo-Html -Head $style -Body $Body
        $emailFrom = ""
        $subject = "$ScriptName returned results"
        $message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody)
        $message.IsBodyHTML = $true
        $smtpServer = ""
        $smtp = New-Object Net.Mail.SmtpClient($smtpServer)

I fully understand that this is reinventing the wheel, but in less than an hour, including the time it took to get assistance with removing the additional columns from the DataRow object from Twitter, the problem was solved, and a time consuming manual process was automated. 

Published Tuesday, May 25, 2010 2:24 AM by Jonathan Kehayias
Filed under:



Nick Weber @Toshana said:


I have a similar dilemma here.  This might help out a lot great work.


May 25, 2010 11:36 PM

Chris said:

Thanks so much for this script!  I've also been wanting to do this for years :)

One question: What would cause this error?

"Cannot index into a null array."



May 26, 2010 12:41 PM

Chris said:

Nevermind, I figured it out: The tabs in the .sql file example were lost during a copy/paste ;)

May 26, 2010 9:39 PM

Jonathan Kehayias said:

Hey Chris,

Sorry about the delay, I've been battling Spam in my comments the last 48 hrs, and deleted all the notices, which I guess your first comment fell in.

The "Cannot index into a null array" error will trip if the header of the file doesn't fit the format listed.  It could also trip if the $result.Tables object is null in:

   if ($result.Tables[0].Rows.Count -ne 0)

So far this hasn't been a big issue for me, but I could do value testing for $null to handle the exceptions in the code.

May 26, 2010 9:56 PM

Chris said:

Thanks Jonathan.

I think this could grow to become a very useful open source application.  This might be the data model for it:

May 27, 2010 12:38 AM

Grant said:

Shouldn't the line

   $subject = "$ScriptName returned results"


   $subject = $ScriptName + " returned results"  

June 1, 2010 2:24 PM

Jonathan Kehayias said:


No, it will imbed the value of $ScriptName inline without having to write the concactenation in old style.

June 18, 2010 2:55 PM

The Rambling DBA: Jonathan Kehayias said:

While catching up on blog comments while I was out I found on my blog post Reinventing the Wheel – Automating

June 18, 2010 3:36 PM
Anonymous comments are disabled

This Blog


Privacy Statement