Powershell -erroraction stop

Tagged: 

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Robin16 Robin16 3 years, 6 months ago.

  • Author
    Posts
  • #10469
    Profile photo of Robin16
    Robin16
    Participant

    Hi Guys,

    I'm trying to stop the PS script execution when i get errors in SQL server database. I'm trying to select a table but if the table doesn't exist in the database, script just throws the errors and continue the rest of the execution. Could you please help me on this ?

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $DataSet = New-Object System.Data.DataSet #-ErrorAction Stop
    $SqlConnection.ConnectionString = "Server = DBATest; Database = master; Integrated Security = True"
    $SqlCmd.CommandText = " use test
    select employee from emp where firstname like '%ton%'"
    $SqlCmd.Connection = $SqlConnection
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlAdapter.Fill($DataSet)|out-null
    $rownum = $Dataset.Tables[0].Rows.Count

    This is just a part of the script where i need to stop the execution and send an email with the error message when the table is not present in the database.
    I'm using -ErrorAction Stop but its isn't working or may be i don't know where exactly i need use that function.

    Thanks in advance

  • #10470
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    -ErrorAction is a common parameter for Cmdlets, but can't be used when you're directly accessing properties and methods of .NET objects. What you'll need to do, in this case, is use a try/catch block to trap the error. In your catch block, you can send out your email and abort the script:

    try
    {
        $SqlAdapter.Fill($DataSet)|out-null
    }
    catch
    {
        # Handle the error here, which is stored in the $_ automatic variable.
        # Send an email, etc.
    
        # The return statement causes your current function or script to immediately exit, rather than proceeding with the next lines of code.
        return
    }
    
    # You only get here if $SqlAdapter.Fill() didn't produce any errors.
    $rownum = $Dataset.Tables[0].Rows.Count
    
    
  • #10472
    Profile photo of Robin16
    Robin16
    Participant

    AWESOME David...its working as expected. I have been working on this issue for hours....thanks a lot for your help..

You must be logged in to reply to this topic.