December 8, 2010 at 10:15 am
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
}
}
January 17, 2011 at 2:38 am
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:
January 17, 2011 at 3:28 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply