March 24, 2014 at 10:14 am
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?
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
March 25, 2014 at 4:17 am
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!!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply