none
try/catch in a foreach loop problem

    Question

  •  

    Hi.

     

    I'm having trouble with try/catch in a foreach loop. I'm trying to get my ps1 to run a SQL script but only against servers it can connect to. Here's what I've done so far; if I don’t use a foreach loop and just point it at one server it works fine…

     

    $server = "SQL01"

     

    try

    {

    invoke-sqlcmd -ServerInstance $server -Query "SELECT @@VERSION" -QueryTimeout 65535 -ea silentlycontinue

    }

    catch

    {

        $_ | Out-Null

        Write-Host "Error connecting to server " $server

    }

     

    As SQL01 is not currently running, I get Error connecton to server SQL01 returned which is what I want. So next I'm trying to run this in a loop against a list of servers ( well two to be precise, SQL01 which isn't running and SQL02 which is)  here is the script to try and do so:-

     

     $file = "get_version.sql"

    $path = "D:\PowerShell\DBA\"

     

    cd $path

     

    $server = (get-content $path\serverlist_test.txt | ? {!$_.Contains("#")})

    foreach ($srv in $server)

    {

    try

     

    {invoke-sqlcmd -ServerInstance $server -input $path\$file -QueryTimeout 65535 -ea silentlycontinue }

     

    catch

    {

                        $_ | Out-Null

                       Write-Host "Error connecting to server " $server

    }

    }

     

    But when I run it I always get the message Error connection to server SQL01 SQL02. I know I have, but am not sure where I've gone wrong here.

     

    Thanks

    Tuesday, June 25, 2013 1:48 PM

Answers

  • Hi,

    In your try/catch, you're using $server instead of $srv.

    • Marked as answer by SQL Rob Tuesday, June 25, 2013 2:29 PM
    Tuesday, June 25, 2013 1:58 PM

All replies

  • Have you verified that $srv actually contains just the server name, and it is not an object with properties?

    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful.

    Tuesday, June 25, 2013 1:53 PM
  • yeah im thinking there is something goofy going on with

    $file = "get_version.sql"

    $path = "D:\PowerShell\DBA\"

     

    cd $path

     

    $server = (get-content $path\serverlist_test.txt | ? {!$_.Contains("#")})

    Tuesday, June 25, 2013 1:56 PM
  • Hi,

    In your try/catch, you're using $server instead of $srv.

    • Marked as answer by SQL Rob Tuesday, June 25, 2013 2:29 PM
    Tuesday, June 25, 2013 1:58 PM
  • Hi,

    In your try/catch, you're using $server instead of $srv.


    Good catch, I didn't even see that lol

    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful.

    Tuesday, June 25, 2013 1:59 PM
  • Hi,

    In your try/catch, you're using $server instead of $srv.


    Good catch, I didn't even see that lol

    If you find that my post has answered your question, please mark it as the answer. If you find my post to be helpful in anyway, please click vote as helpful.


    Nor did I obviously :-) that's fixed it then! Thank you
    Tuesday, June 25, 2013 2:27 PM
  • You're welcome, glad I could help out. I've done that myself more times than I'd like to admit.
    Tuesday, June 25, 2013 2:40 PM