Powershell Try Catch with ExecuteNonQuery()

0

I'm working on a powershell script that will execute a Script across multiple servers. after converting my script to an exe using http://ps2exe.codeplex.com/

I get an error, if any errors are caught in the try catch, when I close the window.

$ErrorActionPreference = 'Continue'
    $dataAdapter = new-object System.Data.SqlClient.SqlCommand ($ExeStatement, $connString);
    $dataAdapter.Connection.Open();
    $dataAdapter.CommandTimeout = 65535;
    Try {
        $dataAdapter.ExecuteNonQuery()}
    catch{
         $ErrorCounter = $ErrorCounter + 1
         $dataAdapter.Connection.Close();}
    finally{
         $dataAdapter.Connection.Close();}

If I remove the try catch I am able to close the window just fine when errors are caught. If I remove the $dataAdapter.CommandTimeout = 65535; I am able to close the window just fine when errors are caught, but I need this as one of the scripts is fairly long. If I set the ErrorActionPreference to STOP it works just fine as well. Though I want to to be able to continue through the errors and report at the end.

I get the following error from windows when closing my window.

Description: Stopped working

Problem signature:
Problem Event Name: CLR20r3
Problem Signature 01: PROGRAMNAME.exe
Problem Signature 02: 0.0.0.0
Problem Signature 03: 53bdc0d9
Problem Signature 04: mscorlib
Problem Signature 05: 2.0.0.0
Problem Signature 06: 5174de33
Problem Signature 07: 34a9
Problem Signature 08: 18c
Problem Signature 09: System.IO.IOException
OS Version: 6.1.7601.2.1.0.16.7
Locale ID: 1033
DefaultDataCollection failed: 0x8007001f

I need the script to be able to continue through the error, but I also need to catch the error.

sql
powershell
error-handling
runtime-error
asked on Stack Overflow Jul 10, 2014 by SecretChief • edited Jun 20, 2020 by Community

1 Answer

0

What happens when you remove the 'finally' block and just close the connection?

        try {
            $dataAdapter.ExecuteNonQuery()
        }
        catch {
            $ErrorCounter = $ErrorCounter + 1
        }
        $dataAdapter.Connection.Close()

or

        try {
            $dataAdapter.ExecuteNonQuery()
            $dataAdapter.Connection.Close()
        }
        catch {
            $ErrorCounter = $ErrorCounter + 1
        }

Does your exec return anything when it completes without error? Maybe alter it to return a 0 or something when it's done running. My guess is the adapter is waiting for something to return (even though it's not a query), therefore if there's no error you never reach the finally block and close the connection.

answered on Stack Overflow Jul 10, 2014 by brendan62269

User contributions licensed under CC BY-SA 3.0