And the Winner is - Get SQL Server Error Messages from PowerShell

And the Winner is - Get SQL Server Error Messages from PowerShell

Rate This
  • Comments 1

I spoke yesterday at a large user group meeting, and we had a prize offered for the best PowerShell script. One question that was asked is how to script out Replication Objects using PowerShell - but that question was answered here already, on our very own MSDN forums:

http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/ebfd651b-5caa-4570-9689-4d57432fd382/

I found that using Live Search. (you don't want to use G**gle for this sort of thing, it will cause you to buy an iPh*ne and start trying to be trendy and hip. Plus it causes rashes.:)

Anyhow, we did have a winner - Trevor Barkhouse sent in a script to read SQL Server Error Message Strings from PowerShell (the ones in sysmessages). Sure, you can do this with a simple query, but I like the format and structure he's put into this script - and I've cribbed a few ideas that I may "borrow" for myself.

It's included below - note that there are two parts (and as always, install on a test system, understand the script before you run it, your mileage may vary, use at your own risk, I'm not responsible if you break your system because you ran some random script from the Internet, etc.):

Get-SQLServerErrorMessages.ps1 - This is the main script

Format-SQLServerErrorMessages.xslt - Required for the formatting. Both sets of code are below.

################################################################################
#   File            :   Get-SQLServerErrorMessages.ps1
#
#   Summary         :   This script looks up a series of error numbers in the 
#                       [sys].[messages] catalog view in SQL Server and 
#                       displays the results in Internet Explorer.
#
#                       NOTE: This script depends on Windows authentication.
#
#   Requirements    :   Internet Explorer
#                       SQL Server 2005 or later
#                       "Format-SQLServerErrorMessages.xslt"
#
#   Parameters      :   $InstanceName (System.String)
#                           The name of the SQL Server instance to use when 
#                           looking up the error messages.  The instance 
#                           be version 9 (2005) or later.
#
#                       $ColumnSeparator (System.String)
#                           The column separator character for the SQLCmd 
#                           utility.
#
#                       $PathToStyleSheet (System.String)
#                           The path to the XSLT style sheet that formats the 
#                           output as an HTML document.  It's not necessary; 
#                           if it can't be found, then the XML is displayed.
#
#                       $LanguageCode (System.String)
#                           The "Locale ID" that specifies the language in 
#                           which the error messages should be displayed.  See 
#                           the following web page for a list of values: 
#                               "List of Locale ID (LCID) Values as Assigned by Microsoft" <http://www.microsoft.com/globaldev/reference/lcid-all.mspx>
#
#                       $Mode (System.String)
#                           The mode in which the script utility should run: 
#                               "CreateShortcut"
#                                   Create a shortcut on the users desktop to 
#                                   launch the script utility (in "Lookup" 
#                                   mode).
#                               "Lookup"
#                                   Look up the error messages for the 
#                                   specified SQL Server error numbers.
#
#   History         :   2009-01-27  Trevor Barkhouse                Created
################################################################################
 
Param(
    [String]$InstanceName = '',
    [String]$ColumnSeparator = '~',
    [String]$PathToStyleSheet = 'Format-SQLServerErrorMessages.xslt',
    [String]$LanguageCode = '0x0409',
    [String]$Mode = 'Lookup'
);
 
 
##  Main  ##
 
################################################################################
#   Function        :   Main
#
#   Summary         :   This is the central function for the script utility.
#
#   Parameters      :   None
#
#   Returns         :   Nothing
#
#   History         :   2009-01-27  Trevor Barkhouse                Created
################################################################################
Function Main {
    #   Determine whether to create a shortcut to the script and exit.
    If ($Mode -eq 'CreateShortcut') {
        Create-Shortcut;
 
        Exit;
    }
 
    #   Get the list of error numbers for the lookup.
    [String]$ErrorNumbers = Get-ErrorNumbers;
 
    #   Look the error numbers up in SQL Server.
    $SQLCmdOutput = Query-SQLServer $ErrorNumbers $LanguageCode;
 
    #   Convert the output from the SQLCmd utility into XML or HTML.
    $OutputFileName = Format-Output $SQLCmdOutput;
 
    #   Display the output file in a new instance of Internet Explorer.
    Display-Output $OutputFileName;
}
 
 
##  Member Functions  ##
 
################################################################################
#   Function        :   Create-Shortcut
#
#   Summary         :   This function creates a shortcut on the users desktop 
#                       to invoke the script.
#
#   Parameters      :   None
#
#   Returns         :   Nothing
#
#   History         :   2009-01-27  Trevor Barkhouse                Created
################################################################################
Function Create-Shortcut {
    #   Look up the path to the user's desktop directory.
    $Desktop = [System.Environment]::GetFolderPath("Desktop");
 
    #   Create the file name for the new shortcut.
    $ShortcutFileName = $Desktop + "\Look up SQL Server error messages.lnk";
 
    #   Verify that there isn't already a shortcut with the given name.
    If ((Test-Path -Path $ShortcutFileName) -eq $false) {
        #   Get the path to this script utility.
        $ScriptFileName = $MyInvocation.ScriptName;
 
        #   Attempt to determine the name of the SQL Server instance to use 
        #   when looking up the error messages.
        $SQLServerInstanceName = Get-SQLServerInstanceName;
 
        #   Add a prefix to the instance name to force the connection to use 
        #   Local Procedure Calls (inter-process communication).
        #   NOTE: This should be disabled if a remote SQL Server instance is 
        #   being used.
        $SQLServerInstanceName = "LPC:" + $SQLServerInstanceName;
 
        #   Instantiate a WScript.Shell COM object (to create the shortcut).
        $WSHShell = New-Object -COM "WScript.Shell";
 
        #   Create, configure, and save the shortcut.
        $Shortcut = $WSHShell.CreateShortcut($ShortcutFileName);
        $Shortcut.WorkingDirectory = Split-Path -Path $ScriptFileName -Parent;
        $Shortcut.TargetPath = "$($PSHome)\PowerShell.exe";
        $Shortcut.Arguments = "& '$ScriptFileName' '$SQLServerInstanceName'";
        $Shortcut.IconLocation = "PowerShell.exe, 0";
        $Shortcut.Save();
 
        #   Output a status message.
        Write-Host "A shortcut has been added to the desktop.";
    }
    Else {
        #   Raise a terminal error.
        Throw "A shortcut named `"Look up SQL Server error numbers.lnk`" already exists on the desktop.";
    }
}
 
################################################################################
#   Function        :   Display-Output
#
#   Summary         :   This function loads the output (HTML or XML document) 
#                       in a new instance of Internet Explorer.
#
#   Parameters      :   $OutputFileName (System.String)
#                           The path to the HTML or XML file that should be 
#                           shown to the user.
#
#   Returns         :   Nothing
#
#   History         :   2009-01-27  Trevor Barkhouse                Created
################################################################################
Function Display-Output (
    $OutputFileName = $(Throw 'The $OutputFileName parameter is required.')
) {
    #   Instantiate a new Internet Explorer object.
    $IE = New-Object -COM InternetExplorer.Application;
 
    #   Load the output file.
    $IE.Navigate2($OutputFileName);
 
    #   Wait until the output file has finished loading.
    While ($IE.Busy)  {
        Sleep -Milliseconds 50;
    }
 
    #   Make the new instance of Internet Explorer visible.
    $IE.Visible = $True;
 
    #   Delete the output file.
    Remove-Item -Path $OutputFileName;
}
 
################################################################################
#   Function        :   Format-Output
#
#   Summary         :   This function converts the output from the SQLCmd 
#                       utility into XML and, possible, HTML.
#
#   Parameters      :   $SQLCmdOutput (System.Object[])
#                           The output captured from the call to the SQLCmd 
#                           utility.
#
#   Returns         :   (System.String)
#                           The path to the output HTML or XML file.
#
#   History         :   2009-01-27  Trevor Barkhouse                Created
################################################################################
Function Format-Output (
    $SQLCmdOutput = $(Throw 'The $SQLCmdOutput parameter is required.')
) {
    #   A local variable to hold the value that will be returned to the 
    #   calling context.
    $OutputFileName = "";
 
    #   Create a name for the temporary XML file.
    $XMLFileName = [System.IO.Path]::ChangeExtension(([System.IO.Path]::GetTempFileName()), ".xml");
 
    #   Obtain a reference to the header row of the SQLCmd output.
    [String]$HeaderRow = $SQLCmdOutput[0];
 
    #   Instantiate two StringBuilder objects for constructing the script's 
    #   output.
    $RowTemplate_SB = New-Object -TypeName System.Text.StringBuilder;
    $Output = New-Object -TypeName System.Text.StringBuilder;
 
    #   Create an array of column names.
    $ColumnNames = $HeaderRow.Split($ColumnSeparator);
 
    #   Create a template for the XML node that represents each error message.
    [Void]$RowTemplate_SB.Append("<message>");
    For ($ColumnCounter = 0; $ColumnCounter -lt $ColumnNames.Count; $ColumnCounter++) {
        [Void]$RowTemplate_SB.AppendFormat(
            "<{0}><![CDATA[{{{1}}}]]></{0}>",
            $ColumnNames[$ColumnCounter],
            $ColumnCounter
        );
    }
    [Void]$RowTemplate_SB.Append("</message>");
 
    #   Reformat the SQLCmd output as XML code.
    [Void]$Output.Append("<?xml version=`"1.0`"?>`n<errorMessages>");
    $RowTemplate = $RowTemplate_SB.ToString();
    For ($LineCounter = 2; $LineCounter -lt $SQLCmdOutput.Count; $LineCounter++) {
        $Values = ([String]$SQLCmdOutput[$LineCounter]).Split($ColumnSeparator);
 
        [Void]$Output.AppendFormat(
            $RowTemplate,
            $Values[0],
            $Values[1],
            $Values[2],
            $Values[3],
            $Values[4]
        );
    }
    [Void]$Output.Append("</errorMessages>");
 
    #   Convert the XML code into an actual XML document.
    $XML = [XML]$Output.ToString();
 
    #   Save the XML document temporary file.
    $XML.Save($XMLFileName);
 
    #   Attempt to transform the XML document into an HTML file.
    If (($PathToStyleSheet -ne "") -and ((Test-Path -Path $PathToStyleSheet) -eq $true)) {
        #   Remove any wild cards in the path and resolve any relative paths 
        #   to the style sheet.
        $PathToStyleSheet = Resolve-Path -Path $PathToStyleSheet;
 
        #   Create a name for the temporary HTML file.
        $HTMLFileName = [System.IO.Path]::ChangeExtension(([System.IO.Path]::GetTempFileName()), ".htm");
 
        #   Transform the XML document into an HTML document.
        $XSLT = New-Object -TypeName System.Xml.Xsl.XslTransform;
        $XSLT.Load($PathToStyleSheet);
        $XSLT.Transform($XMLFileName, $HTMLFileName);
 
        #   Save the name of the HTML file (to be returned to the calling 
        #   context).
        $OutputFileName = $HTMLFileName;
 
        #   Delete the temporary XML file.
        Remove-Item -Path $XMLFileName;
    }
    Else {
        #   Save the name of the XML file (to be returned to the calling 
        #   context).
        $OutputFileName = $XMLFileName;
    }
 
    #   Return the name of the output file to the calling context.
    Return $OutputFileName;
}
 
################################################################################
#   Function        :   Get-ErrorNumbers
#
#   Summary         :   This function requests the error numbers from the user 
#                       and cleanses the input.
#
#   Parameters      :   None
#
#   Returns         :   (System.String)
#                           The comma-separated list of error numbers to look 
#                           up.
#
#   History         :   2009-01-27  Trevor Barkhouse                Created
################################################################################
Function Get-ErrorNumbers {
    #   Prompt the user for the list of error numbers.
    [String]$ErrorNumbers = Read-Host -Prompt "What are the error numbers? (CSV)";
 
    #   Cleanse the input using Regular Expressions.
    $ErrorNumbers = $ErrorNumbers -replace "\p{Po}", ",";
    $ErrorNumbers = $ErrorNumbers -replace "\s", ",";
    $ErrorNumbers = $ErrorNumbers -replace "[^\d,]", "";
    $ErrorNumbers = $ErrorNumbers -replace ",+", ",";
    $ErrorNumbers = $ErrorNumbers -replace "^,", "";
    $ErrorNumbers = $ErrorNumbers -replace ",$", "";
 
    #   Verify that, after cleansing the input, there is still a list of error 
    #   numbers.
    If ($ErrorNumbers -eq "") {
        Throw "The provided list did not contain any numbers.";
    }
 
    #   Return the list to the calling context.
    Return $ErrorNumbers;
}
 
################################################################################
#   Function        :   Get-SQLServerInstanceName
#
#   Summary         :   This function attempts to determine which SQL Server 
#                       instance to use for looking up the error messages.  
#                       Its criteria is the local instance with the highest 
#                       version number.  This is so that the most number of 
#                       error messages will be included.  It also sorts 
#                       (alphabetically) by the instance name, just to make 
#                       the whole thing deterministic.
#
#   Parameters      :   None
#
#   Returns         :   (System.String)
#                           The name of the SQL Server instance.
#
#   History         :   2009-01-28  Trevor Barkhouse                Created
################################################################################
Function Get-SQLServerInstanceName {
    #   The name of the SQL Server instance that will be returned to the 
    #   calling context.
    $InstanceName = "";
 
    #   Load the SMO assembly.
    [Void]([Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo"));
 
    #   Discover the local instances of SQL Server.
    #   NOTE: If you'd like the discovery to include remote servers, change 
    #   the argument from $true to $false.
    #   NOTE: The SQLBrowser service must be started for discovery to work.
    [System.Data.DataTable]$Instances = [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($true);
 
    #   Verify that at least one instance was discovered.
    If ($Instances.Rows.Count -gt 0) {
        #   Create the sort expression for the data table.
        #   NOTE: The [IsLocal] column is involved in case you've made the 
        #   change to include remote servers (mentioned above).
        $SortExpression = "IsLocal DESC, Version DESC, Name DESC";
 
        #   Sort the data table (list of instances).
        $DataView = $Instances.DefaultView;
        $DataView.Sort = $SortExpression;
        $Instances = $DataView.ToTable();
 
        #   Get the name of the first instance that meets the criteria.
        $InstanceName = $Instances.Rows[0].Item("Name");
    }
 
    #   Return the instance name to the calling context.
    Return $InstanceName;
}
 
################################################################################
#   Function        :   Query-SQLServer
#
#   Summary         :   This function executes the query in SQL Server that 
#                       retrieves the error message details for the given 
#                       error numbers.
#
#   Parameters      :   $ErrorNumbers (System.String)
#                           The comma-separated list of error numbers to look 
#                           up.
#                       $LanguageCode (System.String)
#                           The LCID for the language in which the messages 
#                           should be retrieved.
#
#   Returns         :   (System.Object[])
#                           The output from the execution of the SQLCmd 
#                           utility.
#
#   History         :   2009-01-27  Trevor Barkhouse                Created
################################################################################
Function Query-SQLServer (
    [String]$ErrorNumbers = $(Throw 'The $ErrorNumbers parameter is required.'),
    [String]$LanguageCode = $(Throw 'The $LanguageCode parameter is required.')
) {
    #   Create the T-SQL query (in a somewhat human-readable manner).
    $Query = "SELECT * `n" +
        "FROM [sys].[messages] `n" +
        "WHERE `n" +
        "( `n" +
        "    ([message_id] IN ($ErrorNumbers)) `n" +
        "    AND `n" +
        "    ([language_id] = $LanguageCode) `n" +
        ") `n" +
        "ORDER BY [message_id];"
 
    #   Remove the human-readable aspects to make the query more command-line 
    #   friendly.
    #   NOTE: This is superfluous, but has been useful in other scripts.
    $Query = $Query -replace "\n", " ";
    $Query = $Query -replace "\s+", " ";
    $Query = $Query -replace "\( \(", "((";
    $Query = $Query -replace "\) \)", "))";
 
    #   Make sure that the row count for the query isn't returned.
    $Query = "SET NOCOUNT ON; " + $Query;
 
    #   Create the actual command-line for the SQLCmd call.
    $SQLCmdCommandLine = 'SQLCmd -E -S ' +
        $InstanceName +
        ' -r1 -Q "' +
        $Query +
        '" -b -s' +
        $ColumnSeparator +
        ' -W;';
 
    #   Execute the command-line and save the results.
    $SQLCmdOutput = Invoke-Expression -Command $SQLCmdCommandLine;
 
    #   Return the results to the calling context.
    Return $SQLCmdOutput;
}
 
 
##  Entry Point  ##
 
#   Kick off the script utility.
Invoke-Expression -Command Main;
<?xml version="1.0" encoding="UTF-8" ?>
<!--
////////////////////////////////////////////////////////////////////////////////
//  StyleSheet  :   Format-SQLServerErrorMessages.xslt
//
//  Purpose     :   This XSLT transformation is used to display SQL Server 
//                  error messages in a table.
//
//  History     :   2009-01-27  Trevor Barkhouse                Created
////////////////////////////////////////////////////////////////////////////////
-->
<xsl:stylesheet
    version="1.0" 
    xmlns:ms="urn:schemas-microsoft-com:xslt"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>
    <!--
    //  The output is a HTML file.
    -->
    <xsl:output
        method="html"
    />
 
    <!--
    ////////////////////////////////////////////////////////////////////////////
    //  Template    :   errorMessages
    //
    //  Purpose     :   This template processes the root element of the XML 
    //                  document.
    //
    //  History     :   2009-01-27  Trevor Barkhouse            Created
    ////////////////////////////////////////////////////////////////////////////
    -->
    <xsl:template
        name="errorMessages"
        match="/errorMessages"
    >
        <html>
            <head>
                <title>SQL Server Error Messages</title>
                <style>
                    body
                    {
                        font-family: Arial;
                    }
 
                    h1
                    {
                        text-align: center;
                    }
 
                    table
                    {
                        border: 1px solid black;
                        border-collapse: collapse;
                        width: 100%;
                    }
 
                    td
                    {
                        border: 1px solid black;
                        border-collapse: collapse;
                        font-size: 10pt;
                        padding-left: 5px;
                        padding-right: 5px;
                    }
 
                    th
                    {
                        border: 1px solid black;
                        border-collapse: collapse;
                        font-size: 10pt;
                        padding-left: 5px;
                        padding-right: 5px;
                        vertical-align: bottom;
                        white-space: nowrap;
                    }
                </style>
            </head>
            <body>
                <h1>SQL Server Error Messages</h1>
                <table>
                    <tr>
                        <th>ID</th>
                        <th>Language ID</th>
                        <th>Severity</th>
                        <th>Is Event Logged</th>
                        <th>Text</th>
                    </tr>
                    <xsl:apply-templates select="message" />
                </table>
            </body>
        </html>
    </xsl:template>
 
 
    <!--
    ////////////////////////////////////////////////////////////////////////////
    //  Template    :   message
    //
    //  Purpose     :   This template processes each message node.
    //
    //  History     :   2009-01-27  Trevor Barkhouse            Created
    ////////////////////////////////////////////////////////////////////////////
    -->
    <xsl:template
        name="message"
        match="message"
    >
        <tr>
            <td style="text-align: center;"><xsl:value-of select="message_id" /></td>
            <td style="text-align: center;"><xsl:value-of select="language_id" /></td>
            <td style="text-align: center;"><xsl:value-of select="severity" /></td>
            <td style="text-align: center;"><xsl:value-of select="is_event_logged" /></td>
            <td><xsl:value-of select="text" /></td>
        </tr>
    </xsl:template>
</xsl:stylesheet>
Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post