How I Fix PowerShell Generic ExecuteNonQuery SQL Error My Scripts

When working with SQL Server from PowerShell using the Microsoft.SqlServer.Management.Common.ServerConnection object, I’ve often encountered vague error messages when a SQL script fails.

I’m going to walk you through my journey. I’ll show you the original code, explain the underlying issue, and expand the script to print better error details along with practical enhancements. This will help you, as it helped me, confidently handle SQL exceptions in your automation scripts.

The Original PowerShell Code

I started with a basic script that establishes a connection to SQL Server and executes a SQL script. My code looked like this:

# Establishing SQL Server connection
$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection

# Assign SQL script (this script causes an error by referencing a non-existent table)
$ScriptToRun = "SELECT * FROM NonExistentTable;"

# Try to run the script
Try {
    $conn.ExecuteNonQuery($ScriptToRun)
}
Catch {
    Write-Output $_.Exception.Message
    $conn.RollBackTransaction()
    throw $_.Exception
}

At first glance, everything appears straightforward. However, when the script encounters an error for instance, when the table doesn’t exist the error message I received was disappointingly generic:

Error Handling

Catch
    {
        Write-Output $_.Exception.Message

        $conn.RollBackTransaction()
        throw $_.Exception

    }

Exception calling “ExecuteNonQuery” with “1” argument(s): “An exception occurred while executing a Transact-SQL statement or batch.”

This generic response is not very useful when trying to debug issues or log detailed error information.

The Problem

I expected to see a specific error message that pinpoints exactly what went wrong. Unfortunately, the basic error handling was only outputting the top-level exception message. This led me to realize that I was missing out on the rich details provided by the SQL Server, such as:

• Line Number: Which line in the script triggered the error.
• Procedure Name: If the error occurred within a stored procedure.
• Exact SQL Server Error Code: That can be used to further diagnose the issue.

The root of the problem is that $_ in PowerShell refers to the error object. Accessing $.Exception.Message only provides the top-level .NET exception message, not the underlying SQL error details nested in inner exceptions.

Why This Happens

SQL Server exceptions frequently contain an InnerException or even an InnerException.InnerException that holds the actual, detailed SQL error. Simply reading $_ .Exception.Message misses this critical information. To get to the bottom of it, you need to dive into these nested exception objects to extract the finer details of the error.

Print the Inner Exception Details

To overcome this limitation, I enhanced the catch block in my script to recursively check for and print inner exceptions. Here’s the improved version:

{
    # Print the top-level exception
    Write-Output "Top-level Error: $($_.Exception.Message)"

    # Check and print the inner exception if it exists
    if ($_.Exception.InnerException) {
        Write-Output "Inner Error: $($_.Exception.InnerException.Message)"

        # Further check for deeper SQL Server error details
        if ($_.Exception.InnerException.InnerException) {
            Write-Output "SQL Server Error: $($_.Exception.InnerException.InnerException.Message)"
        }
    }

    # Rollback transaction if needed (this check is optional, based on your connection context)
    if ($conn.ConnectionContext.SqlExecutionModes -contains "ExecuteSql") {
        $conn.RollBackTransaction()
    }

    # Optional: log the full exception for debugging purposes
    Write-Output "Full Stack Trace: $($_.Exception.ToString())"

    throw $_.Exception
}

With this change, when an error occurs, I now see a cascade of messages that allow me to identify the precise cause of the error. This level of detail has been invaluable in diagnosing problems quickly.

Practice Enhancements

Over time, I found more ways to improve my PowerShell scripts, making them more robust and easier to maintain. Here are some practical enhancements that I integrated:

Add Transaction Support

In many scenarios, you need to ensure atomic operations across multiple SQL commands. Here’s how I wrapped my SQL commands in a transaction:

$conn.BeginTransaction()
Try {
    $conn.ExecuteNonQuery("UPDATE Table1 SET Col = 'Value' WHERE ID = 1")
    $conn.CommitTransaction()
}
Catch {
    $conn.RollBackTransaction()
    Write-Output "Error: $($_.Exception.InnerException.InnerException.Message)"
}

Log Errors to a File

To maintain a record of errors for post-mortem analysis, logging is key. I added a simple logging mechanism that appends error details to a file:

-Content -Path "error_log.txt" -Value "$(Get-Date): $($_.Exception.ToString())"

This ensures that even if the console output is missed, the error details are safely stored.

Use Parameters in Queries

In another project, I needed to use parameterized queries to avoid SQL injection and to handle dynamic values safely. Here’s how I achieved that:

$cmd = $conn.ConnectionContext.SqlConnectionObject.CreateCommand()
$cmd.CommandText = "UPDATE Users SET Name = @Name WHERE ID = @ID"
$cmd.Parameters.Add("@Name", [System.Data.SqlDbType]::VarChar).Value = "Zoya"
$cmd.Parameters.Add("@ID", [System.Data.SqlDbType]::Int).Value = 123
$cmd.ExecuteNonQuery()

This approach not only secures your SQL queries but also makes them more adaptable to changes.

Final Thought

PowerShell’s ExecuteNonQuery() is a powerful method for executing Transact-SQL statements, but its built-in error reporting can be quite limiting. Through my own experiences, I learned that by delving into the nested inner exceptions and logging detailed error messages, I could significantly improve the transparency and debuggability of my SQL scripts. The practical enhancements such as transaction management, error logging, and safe parameterized queries have further strengthened my automation workflows.