Powershell SMO / Nested Error logic

Question

I am trying to understand how to handle nested error logic in powershell. If you call a function from another function and an error occurs, do the errors flow back up the chain to the main? Or I guess I should ask how to do that. For example, I have a send-mail function that is called from another function if an error occurs, but what happens if an error occurs inside that call. Or I have a function that looks for the lastbackup device that calls a new-server function...etc. Code pasted below. Yes, I got some of the logic from other places on the web. In those examples people had provided, they did not always have error catches...etc. So this was my attempt at it. Looking for guidance.

function Get-LastBackupDevice {param([string] $Instance,
                                     [string] $Login,
                                     [string] $Password,
                                     [string] $Catalgoue)     

    #==========================================================================
    # Local Variables
    #==========================================================================

    [string] $Message = ""    
    [string] $Subject = ""
    [string] $Device = ""

    #==========================================================================
    # Parameter Validation
    #==========================================================================
    try
    {    
        $Server = New-SMOServer $Instance    
        $Query = @"
                   declare @Catalogue sysname 
                       set @Catalogue = '$Catalogue'
                       
                    select f.physical_device_name as 'Device'
                      from msdb.dbo.backupset as s with (nolock) inner join msdb.dbo.backupmediafamily as f with (nolock) 
                                                                         on s.media_set_id = f.media_set_id
                     where s.database_name = @Catalogue
                       and f.device_type = 2 
                       and s.backup_finish_date = (select max(backup_finish_date)
                                                      from msdb.dbo.backupset with (nolock)
                                                     where database_name = @Catalogue
                                                       and is_snapshot = 0)
"@
    
        $Catalgoue = $Server.Databases["msdb"]
        $RecordSet = $Catalgoue.ExecuteWithResults($Query)
        $Device = $RecordSet.Tables[0].Rows[0].Item('Device')   
    }
    catch [System.Exception]
    {
        $Ex = $_.Exception            
        $Message = $Ex.GetType().FullName + '(' + $Ex.Message + ')'
        
        $Ex = $Ex.InnerException
        while ($Ex.InnerException)
        {
            $Message = $Message + ' --> ' + $Ex.GetType().FullName + '(' + $Ex.Message + ')'
            $Ex = $Ex.InnerException
        }
            
        $Subject = "Verify-Device: Backup device verification failed. See Message body for details..."
             
        Send-Mail -From:$Sender -To:$Recipients -Subject:$Subject  -Body:$Message      
        
        Return
    }  
    finally
    {
        if ($Connection.IsOpen -eq $true)
        {
            $Server.ConnectionContext.Disconnect()
        }    
    }   
    
    $Device   
}

function New-Server {param([string] $Instance,
                           [string] $Login,
                           [string] $Password,
                           [int] $Timeout = 0)     

    #==========================================================================
    # Local Variables
    #==========================================================================

    [string] $Message = ""    
    [string] $Subject = ""

    #==========================================================================
    # Parameter Validation
    #==========================================================================
     
                     
    #==========================================================================
    # Code Section
    #==========================================================================                              

    try
    {    
        $Connection = New-Object ("Microsoft.SqlServer.Management.Common.ServerConnection")
        $Connection.ServerInstance = $Instance

        if ($Login)
        {
            $Connection.LoginSecure = $false
            $Connection.Login = $Login
            $Connection.Password = $Password
        }
        else
        {
            $Connection.LoginSecure = $true
        }

        $Connection.Connect()
        
        if ($Connection.IsOpen -eq $false)
        {
            throw 'Unable to connect to SQL Server Instance (' + $Instance + ')'
        }
        
        $smoInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $Connection
        $smoInstance.ConnectionContext.StatementTimeout = $Timeout   
    }
    catch [System.Exception]
    {
        $Ex = $_.Exception            
        $Message = $Ex.GetType().FullName + '(' + $Ex.Message + ')'
        
        $Ex = $Ex.InnerException
        while ($Ex.InnerException)
        {
            $Message = $Message + ' --> ' + $Ex.GetType().FullName + '(' + $Ex.Message + ')'
            $Ex = $Ex.InnerException
        }
        
        $Subject = "New-Server: An error has occurred during SMO object creation. See Message body for details..."
              
        Send-Mail -From:$Sender -To:$Recipients -Subject:$Subject  -Body:$Message      
        
        Return
    }    
    
    $smoInstance                     
}

function Send-Mail {param([string] $From = $Sender,
                          [string] $To = $Recipients,
                          [string] $Subject,
                          [string] $Body,
                          [string] $Attachment)

    #==========================================================================
    # Local Variables
    #==========================================================================
    
    [string] $Message

    #==========================================================================
    # Parameter Validation
    #==========================================================================
    try
    {        
        if (!$Subject)
        {
            throw 'You must specify a subject for the Mail.'
        }
                
        if (!$Body)
        {
            throw 'You must specify some text for the message body.'
        } 
    }
    catch [System.Exception] 
    {
        $Ex = $_.Exception            
        $Message = $Ex.GetType().FullName + '(' + $Ex.Message + ')'
        
        $Ex = $Ex.InnerException
        while ($Ex.InnerException)
        {
            $Message = $Message + ' --> ' + $Ex.GetType().FullName + '(' + $Ex.Message + ')'
            $Ex = $Ex.InnerException
        }
        
        throw $Ex.Message
        
        Return
    }        
       
    
    #==========================================================================
    # Code Section
    #==========================================================================
    
    try
    {
        $Mail = New-Object Net.Mail.MailMessage
        
        $Mail.From = $From
        $Mail.To.Add($To)
        $Mail.Subject = $Subject
        $Mail.Body = $Body
        
        if ($Attachment)
        {    
            $Attach = New-Object Net.Mail.Attachment($Attachment)
            $Mail.Attachments.Add($Attach)
        }
        
        $SMTP = New-Object Net.Mail.SmtpClient("smtp.insight.com")
        $SMTP.Send($Mail)
    }
    catch [System.Exception]
    {
        $Ex = $_.Exception            
        $Message = $Ex.GetType().FullName + '(' + $Ex.Message + ')'
        
        $Ex = $Ex.InnerException
        while ($Ex.InnerException)
        {
            $Message = $Message + ' --> ' + $Ex.GetType().FullName + '(' + $Ex.Message + ')'
            $Ex = $Ex.InnerException
        }
        
        throw $Ex.Message      
        
        Return        
    }
}

---

John M. Couch

Answer 1

Since SMO loves nested exceptions I'll just return the base exception which is usually the same error message you see when you run the equivalent T-SQL in SSMS.  Here's the typical SMO error handling pattern I'll use in Powershell:

try {
      #Do Some SMO Stuff
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
         throw $message
}

Answer 2

Do you not handle nested Exceptions? I have seen so many variations, and the main is usually similar to yours or Error[0].

---

John M. Couch

Answer 3

The GetBaseException method returns, well the base exception. So yes, this handles nested exceptions throwing the inner most exception which in my testing has been the only useful exception in SMO.

My example isn't similar at all to Error[0] which just returns the outer exception. You could use $error[0] | format-list -force will return all nested exception, but much of the details are needless.

Answer 4

So, when you process errors, you throw from the sub procedure and report out on the outer most I assume?

---

John M. Couch

Answer 5

I'm not sure I understand your question. I'll do something, really anything calling and SMO method and then use the GetBaseException method in the catch block to return the base (inner most exception) which is really the only meaningful exception in SMO. My SqlProxy module is good example of where I've used it:

http://poshcode.org/3077

Answer 6

I guess I am looking more at using the PowerShell script to be run form SQL Server Agent and send a notification as well as write to the event log if an error occurs. for instance, if I am backing up 30 databases on an instance, and one backup fails, I don't want the script to fail out, I want to be notified an error occurred and continue to the next database. I have a main routine that calls functions, and performs the work...etc. So if the main routine calls a backup function that issues a backup to SQL Server and it receives an error (fails) the function should report back to the main routine, send out the failure notification and move on to the next database.

---

John M. Couch

Answer 7

And BTW, your code was the foundation for my backup script. And yes, I documented in the header where it came from and provided a link to your script...etc.  :)

---

John M. Couch

Answer 8

In SQL Server Agent if you run a Powershell job step or a cmdexec job step which executes powershell.exe, the behavior of continuing on error in Powershell is on by default. By this I mean $ErrorActionPreference is set to Continue by default, so a Powershell script which encounters an error wil continue unless you set $ErrorActionPreference to Stop or use -ErrorAction Stop.

I don't think you'll need to worry about continuing part.

If your main controller script should see the errors bubble up. If you have two script:

Script 1 (sub):

try {
      #Do Some SMO Stuff
    }
    catch {
        $message = $_.Exception.GetBaseException().Message
         throw $message
}

Then Script 2 (main):

try {
    script1 $yourparameter
}
catch {
    #Log Stuff
    $message = "$_ `n $yourparameter"
    write-eventlog -logname Application -source myapp -eventID 1 -message $message -EntryType Information
}

Answer 9

Glad I could help, some of my older scripts on CodePlex http://sqlpsx.codeplex.com use the while loop technique to get toinner exceptions, but moved I to GetBaseException on newer ones.

Answer 10

One of the main reasons I asked about this is that I have a problem with powershell executing the SQL backups ATM. The backups are randomly failing with a very simple error message, as seen below:

Backup failed for Server 'HQIOSQLTRN02\TRN02'.  --> An exception occurred while executing a Transact-SQL statement or batch.

That is what is returned from the nested loop error catch. Yet, if I look in the error log on the server, I see this:

BackupIoRequest::ReportIoError: write failure on backup device '\\Backups\MyDB.Compression.Full.09.16.2012-16.00.02.bak'. Operating system error 64(The specified network name is no longer available.).

BACKUP failed to complete the command BACKUP DATABASE MyDB. Check the backup application log for detailed messages.

The operating system returned the error '64(The specified network name is no longer available.)' while attempting 'SetEndOfFile' on '\\Backups\MyDB.Compression.Full.09.16.2012-16.00.02.bak'.

The operating system returned the error '64(The specified network name is no longer available.)' while attempting 'FlushFileBuffers' on '\\Backups\MyDB.Compression.Full.09.16.2012-16.00.02.bak'.

The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'DeleteFile' on '\\Backups\MyDB.Compression.Full.09.16.2012-16.00.02.bak'.

I am not so much worried about the error itself, just wondering why powershell only returns that simple error message and not the whole thing listed above? I thought maybe I was missing something. Also, I am trying to understand the flow in POwerShell a bit more to write better code and error logic.

---

John M. Couch

Answer 11

Thanks cmille19. You resolved both my issues. The GetBaseException method returns the actual messages I was missing using the nested loop construct, and you gave me the nested error logic direction I was looking for. Thanks!

---

John M. Couch

Answer 12

Glad it worked out.

-- Chad Miller