Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Continue on error in foreach Expand / Collapse
Author
Message
Posted Monday, March 24, 2014 10:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 10:34 PM
Points: 46, Visits: 535
I have PS that select all instance setup in CMS and then using that list does a foreach check for errors in the database mail log. IF all instance connect it runs great but if it cant connect to one instance it fails. I tried to add a try and catch but maybe didnt know where or who and also tried setting $ErrorActionPreference.

Anyone out there able to help me out?


$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.server_group_id = '15' or SSSGI.server_group_id = '14' or SSSGI.server_group_id = '13' or SSSGI.server_group_id = '12'
" -serverinstance "consoleserver"
$results = @()

foreach($instanceName in $instanceNameList)
{$results += Invoke-Sqlcmd -Query "
use msdb
go
SELECT @@servername, items.subject,
items.last_mod_date
,l.description FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > DATEADD(hh, -1,getdate())

" -ServerInstance $instanceName.Name}
$results| Where-Object {$_} | Export-Csv c:\MailFailure.csv -NoTypeInformat



Post #1554111
Posted Tuesday, March 25, 2014 4:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:55 AM
Points: 5,156, Visits: 2,755
Have you tried:

$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.server_group_id = '15' or SSSGI.server_group_id = '14'
or SSSGI.server_group_id = '13' or SSSGI.server_group_id = '12'"
-serverinstance "consoleserver"
$results = @()

foreach($instanceName in $instanceNameList)
{
try
{
$results += Invoke-Sqlcmd -Query
"use msdb;
go;
SELECT @@servername, items.subject, items.last_mod_date,l.description
FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > DATEADD(hh, -1,getdate());"
-ServerInstance $instanceName.Name
}
catch
{
Write-Error $_
}
}
$results | Where-Object {$_} | Export-Csv c:\MailFailure.csv -NoTypeInformat


Edit: Code formatting.


Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1554347
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse