Odd Powershell/SMO behaviour with error handling

  • mazzz

    SSCertifiable

    Points: 6139

    Hi folks

    I hope someone can shed some light on this. I had a powershell v2.0 script that looped through a list of servers, then through each server's databases, and then did something or other. Its error handling worked fine.

    Then I took a copy of the script, and replaced the foreach database statement with a foreach SQLAgent job statement instead, and equivalent errors weren't trapped (or at least it didn't look like the catch section was being hit)

    In the script below, as you can see I have a long list of commented out foreach stements. My file C:\Servers.txt has just one entry in it, the name of a SQL Server that doesn't exist, I would expect it to fail when calling $srv.Databases with the error "A network-related or instance-specific error occurred while establishing a connection to SQL Server. ..."

    Now, if I run the script with any of the foreach statements that have "#error is output" next to them, the error is output as expected.

    If, however I run the script with one of the foreach statements next to which I have typed "#no output", no error is output.

    1. Can anyone reproduce this, or is it something wrong with my setup, maybe?

    2. If you can reproduce this, how are some collections throwing an error and some not?

    Many thanks, I am well and truly baffled!

    cls

    # load assemblies

    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")

    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

    $serverfile = "c:\Servers.txt"

    $userName = "sqldba"

    $password = "b14hb14h"

    # get server names

    $servers = Get-Content $serverfile

    # loop through all SQL Servers

    foreach($srvname in $servers){

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($srvname)

    $srv.ConnectionContext.NonPooledConnection = "True"

    # login using SQL authentication, supplying the username and password

    $srv.ConnectionContext.LoginSecure=$false;

    $srv.ConnectionContext.set_Login($userName)

    $srv.ConnectionContext.Password = $password

    try{

    $srvname

    foreach($j in $srv.Databases) #error is output

    #foreach($j in $srv.BackupDevices) #error is output

    #foreach($j in $srv.Credentials) #error is output

    #foreach($j in $srv.Endpoints) #error is output

    #foreach($j in $srv.Languages) #error is output

    #foreach($j in $srv.LinkedServers) #error is output

    #foreach($j in $srv.Logins) #error is output

    #foreach($j in $srv.Roles) #error is output

    #foreach($j in $srv.SystemDataTypes) #error is output

    #foreach($j in $srv.SystemMessages) #error is output

    #foreach($j in $srv.UserDefinedMessages) #error is output

    #foreach($j in $srv.Jobserver.Jobs) #no output

    #foreach($j in $srv.CryptographicProviders) #no output

    #foreach($j in $srv.Events) #no output

    #foreach($j in $srv.Processors) #no output

    #foreach($j in $srv.Properties) #no output

    #foreach($j in $srv.ServerAuditSpecifications) #no output

    #foreach($j in $srv.Triggers) #no output

    {

    }

    }

    catch{

    $err = $Error[0].Exception

    while ( $err.InnerException )

    {

    $err = $err.InnerException

    }

    $srvname + " : " + "CAUGHT: " + $err.Message

    continue

    }

    }

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • Martin Bell-290511

    SSC Enthusiast

    Points: 161

    SMO is not consistent when raising exceptions for login failures.

    I've blogged about Powershell error handling:

    http://sqlblogcasts.com/blogs/martinbell/archive/2011/01/16/Powershell-Error-Handling.aspx

    and raised this error on connect:

    http://sqlblogcasts.com/blogs/martinbell/archive/2011/01/17/SMO-doesn_2700_t-always-raise-an-error-when-handling-login-failures.aspx

  • mazzz

    SSCertifiable

    Points: 6139

    Thanks Martin!

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply