Continue on error in foreach

  • 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

  • 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