Retrieving SQL Server InfoMessages in PowerShell 5

Question

The following PowerShell function worked correctly on PowerShell 2 but now I'm testing it on PowerShell 5 and can't make it work. It is meant to automate backup restore tests. But since SQL Server backup related tasks use messages instead of returning a recordset I had to add a add_InfoMessage with a handler.

I think I could do it with Invoke-Sqlcmd -Verbose but I prefer this method.

I've also searched how to do it with SMO but found nothing

function Restore-Test {
    param (
        [string[]] $bkpPath,
        [string[]] $testServer
    )
    $message = "Inicio: " + (get-date);
    $message += "`nArchivo: " + $bkpPath;
    $message += "`nTestServer: " + $testServer;
    $connString = "Server=" + $testServer + ";Database=master;Integrated Security=SSPI;";
    $commandString = "print 'Message from MSSQL!';";
    #$commandString = "DBA_TOOLS.dbo.sp_RestoreTest N'" + $bkpPath + "'";
    $conn = New-Object System.Data.SqlClient.SqlConnection $connString;
    $conn.Open();
    $cmd = $conn.CreateCommand(); 
    $cmd.CommandText = $commandString ; 
    $handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $message += "`n" + $event.Message };
    $conn.add_InfoMessage($handler); 
    $conn.FireInfoMessageEventOnUserErrors = $true;
    $cmd.CommandTimeout = 0;
    $cmdResult = $cmd.ExecuteNonQuery();
    $conn.Close();
    $message += "`nExecution result: " + $cmdResult + "`nFinalizacion: " + (get-date);

    return $message;
}
export-modulemember -function Restore-Test

Answer 1

You will not be able to capture the print statements in the manner you are trying. The handler is meant to output the statement to the console, not to actually capture it.

This would cause the message to write the console, but you will never (at least my testing) be able to append this into your $message variable. Which I don't recall this working like you have it in PowerShell 2.0, but I don't have that version available anymore to try.

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };

Now why this has changed I have not found yet. If I ever do I will try to remember to come update this post.

Answer 2

I finally realized the problem was variable scope. The way around it is to define it as global $global:message = "" and use it inside the event handler with the same reference:

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) $global:message += "`n" + $event.Message };

By the way I tried with Invoke-Sqlcmd -Verbose but it has trouble with SQL errors.

You can do it this way:

$message = ""
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $testServer
$ps = [PowerShell]::Create() 
$com = $ps.AddCommand( "Invoke-Sqlcmd" )
$com.AddParameter( "ServerInstance", $serverInstance ) | Out-Null
$com.AddParameter( "Query", $commandString ) | Out-Null
$com.AddParameter( "QueryTimeout", 0 ) | Out-Null
$com.AddParameter( "OutputSqlErrors", $True ) | Out-Null
$com.AddParameter( "IncludeSqlUserErrors") | Out-Null
$com.AddParameter( "SeverityLevel", 0) | Out-Null
$com.AddParameter( "Verbose" )  | Out-Null
$sqlret += $ps.Invoke()  #| Out-Null
$ps.Streams.Verbose | % { $message += "`n" + $_.Message }
$ps.Streams.Error   | % { $message += "`n" + $_.Exception.Message }

Answer 3

To capture all of the various parts of the SQL error message (line number, proc name, etc.), you can define the handler something like this:

$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
    param($sender,$event)
    ForEach ($sqlError in $event.Errors)
    {
        If ($sqlError.Class -gt 0) # Class here corresponds to the severity level in the SQL error
        {
            $global:message = ""
            $global:message +=      "Source     = "+$sqlError.Source.ToString()
            $global:message += "`n"+"Server     = "+$sqlError.Server.ToString()
            $global:message += "`n"+"Class      = "+$sqlError.Class.ToString()
            $global:message += "`n"+"State      = "+$sqlError.State.ToString()
            $global:message += "`n"+"Number     = "+$sqlError.Number.ToString()
            $global:message += "`n"+"LineNumber = "+$sqlError.LineNumber.ToString()
            $global:message += "`n"+"Procedure  = "+$sqlError.Procedure.ToString()
            $global:message += "`n"+"Message    = "+$sqlError.Message.ToString()
        }
    }
}