Exception calling "ExecuteNonQuery" with "0" argument(s): "The transaction count is not 0

Question

I am trying to run an Azure Runbook that executes a database maintenance stored procedure. However, $command.ExecuteNonQuery() gives me the error Exception calling "ExecuteNonQuery" with "0" argument(s): "The transaction count is not 0 in the following PowerShell script:

$credential = Get-AutomationPSCredential -Name 'Credential'

$userName = $credential.UserName

$password = $credential.GetNetworkCredential().Password

$connectionString = "Data Source=server.database.windows.net;Initial Catalog=database;Integrated Security=False;User ID=$userName;Password=$password"

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

$connection.Open()

$transaction = $connection.BeginTransaction()

try {
    $command = New-Object System.Data.SqlClient.SqlCommand

    $command.Connection = $connection

    $command.Transaction = $transaction
    
    $command.CommandType = [System.Data.CommandType]::StoredProcedure
    
    $command.CommandText = "dbo.IndexOptimize"
    
    $command.Parameters.AddWithValue("@Databases", "USER_DATABASES")
    
    $command.Parameters.AddWithValue("@MinNumberOfPages", "500")
    
    $command.Parameters.AddWithValue("@FragmentationMedium", "INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE")
    
    $command.Parameters.AddWithValue("@FragmentationHigh", "INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE")
    
    $command.Parameters.AddWithValue("@FragmentationLevel1", "5")
    
    $command.Parameters.AddWithValue("@FragmentationLevel2", "30")
    
    $command.Parameters.AddWithValue("@UpdateStatistics", "COLUMNS")
    
    $command.Parameters.AddWithValue("@LogToTable", "Y")
    
    $command.ExecuteNonQuery()
    
    $transaction.Commit()
}
catch {
    try {       
        $transaction.Rollback()
    }       
    catch {
        # Microsoft recommends to catch and log and/or ignore rollback exceptions
    }
    throw
}
finally {
    $command.Dispose()
    
    $transaction.Dispose()
    
    $connection.Close()
}

Answer 1

Ola's maintenance scripts do their own transaction handling. So you don't need your own transaction.

Remove the following lines as well as the catch:

$transaction = $connection.BeginTransaction()
...
$command.Transaction = $transaction
...
$transaction.Dispose()

Also the whole catch... $transaction.Rollback was not necessary anyway even if you wanted a transaction. Since you have a finally with $transaction.Dispose this will get handled for you anyway.