August 25, 2011 at 12:18 pm
Good morning.
I have a very simple PowerShell script that runs through a list of SQL instances and runs a query.
$results = @()
foreach($instanceName in $instanceNameList)
{
$results += Invoke-Sqlcmd -Query "
select @@SERVERNAME
" -ServerInstance $instanceName.Name
}
trap [Exception] {continue}
$results| Where-Object {$_} | Export-Csv E:\DBA\PowerShell\results.csv -NoTypeInformation
This outputs the result of the query to a text file. I have a trap line in there so that if an instance is offline, the script keeps going to give me the rest of the output. My questions is, can I output to a different file, the names of the instances that were offline and handled by that trap exception? That way I could send an alert out that the servers did not respond.
August 26, 2011 at 9:01 am
Have you tried instead of using a trap using a try/catch block? That way if the code in the try block threw an error you could define in the catch block what you wanted to do. For more info run this in your ps session:
get-help about_try_catch_finally
Joie Andrew
"Since 1982"
August 26, 2011 at 1:58 pm
Thanks for the suggestion. I have tried to work it into the script like this but my PowerShell skills are weak. Should the try be looking for any error condition and then the catch writes the name of the server to $offline?
$instanceNameList = invoke-Sqlcmd -query "
SELECT [server_name] as Name
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id
where SSSGI.parent_id = '6'
" -serverinstance "DBAServer"
$results = @()
$offline= @()
foreach($instanceName in $instanceNameList)
{
$results += Invoke-Sqlcmd -Query "
select @@SERVERNAME
" -ServerInstance $instanceName.Name
}
try {trap [exception]{continue}}
catch {$offline += $instanceName}
$offline| Where-Object {$_} | Export-Csv E:\DBA\PowerShell\Online\Offline.csv -NoTypeInformation
$results| Where-Object {$_} | Export-Csv E:\DBA\PowerShell\Online\Online.csv -NoTypeInformation
August 26, 2011 at 3:40 pm
I found this article which helped me accomplish what I wanted to do.
August 26, 2011 at 4:45 pm
Good you found a solution that works for you. As for your previous post, you would use the try/catch block instead of a trap, not with it. So it would be something more like this:
$instanceNameList = invoke-Sqlcmd -query "
SELECT [server_name] as Name
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id
where SSSGI.parent_id = '6'
" -serverinstance "DBAServer"
$results = @()
$offline= @()
try
{
foreach($instanceName in $instanceNameList)
{
$results += Invoke-Sqlcmd -Query "
select @@SERVERNAME
" -ServerInstance $instanceName.Name
}
}
catch
{
$offline += $instanceName
$offline| Where-Object {$_} | Export-Csv E:\DBA\PowerShell\Online\Offline.csv -NoTypeInformation
$results| Where-Object {$_} | Export-Csv E:\DBA\PowerShell\Online\Online.csv -NoTypeInformation
}
You can tell it what type of exceptions to look out for, but if you do not it will catch all exceptions (unless PowerShell determines the error is a non-terminating one).
Joie Andrew
"Since 1982"
August 26, 2011 at 4:48 pm
That makes so much more sense now that I see it.
Thank you!
August 26, 2011 at 5:24 pm
Joie, using your example and the example on the web site, I came up with this working PowerShell script. Thanks again.
$ErrorActionPreference = "SilentlyContinue"
$Path = "E:\dba\powershell\online\report.txt"
If (Test-Path $Path) {Remove-Item $Path -recurse}
$date = get-date
$instanceNameList = invoke-Sqlcmd -query "
SELECT 'DBAServer' as Name
UNION
SELECT [server_name] as Name
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id
where SSSGI.parent_id = '6'
" -serverinstance "DBAServer"
foreach ($instanceName in $instanceNameList)
{
try
{
Invoke-Sqlcmd -Query "
select @@SERVERNAME
" -ServerInstance $instanceName.Name -ErrorVariable err
}
catch
{
[string]$Name = $instanceName.Name | Out-String -stream
if ($err.count -gt 1){"SQL Server Instance $Name failed the online check on $Date." | Out-File E:\DBA\Powershell\Online\report.txt -append}
}
}
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply