Multiple Invoke-SqlCmd and Sql Server transaction

Question

How to make sure the following statements running in one transcation?

try
{
  Invoke-SqlCmd -Query 'begin tran; ...sql script...'
  # Other Powershell script
  Invoke-SqlCmd -Query '...sql script...; if @@transcation > 0 commit tran' 
}
catch 
{
   Invoke-SqlCmd -Query 'if @@transaction > 0 rollback tran;'
}

What's the best approach to run Powershell script in a transaction?

Answer 1

Unfortunately, SqlProvider in Powershell doesn't have Transaction Capability like Registry provider. Hence Start-transaction won't work here.

You can go native and utilize TransactionScope class.

Try{
   $scope = New-Object -TypeName System.Transactions.TransactionScope

   Invoke-Sqlcmd -server Darknite -Database AdventureWorks2008 -Query $Query1 -ea stop
   Invoke-Sqlcmd -server Darknite -Database AdventureWorks2008 -Query $Query2 -ea stop

   $scope.Complete() 
}
catch{
    $_.exception.message
}
finally{
    $scope.Dispose() 
}

All Invoke-sqlcmds which are put between $scope assignment and $scope.complete(), would be treated as one transaction. If any of them errors out, all would be rolled back.

Answer 2

Nitesh gives a very good example, helped me, thank you! One thing to add is that Scope.Complete() does not send transaction commit to database. This will be obvious if you use this code in a loop.

The commit is executed on end using for your scope, so a working example to ensure commit would be adding a explicit dispose after complete like so:

$scope.Complete();
$scope.Dispose();

See details TransactionScope Complete() doesn't commit the transaction before exiting the USING statement

Answer 3

Invoke-sqlcmd doesn't support ADO.NET transcations. You have two workarounds, either write the equivalent Powershell code to the C# code shown in this MSDN documentation for ADO.NET transactions: http://msdn.microsoft.com/en-us/library/2k2hy99x(v=vs.110).aspx

Or use T-SQL transactions. A quick way of doing this without adding T-SQL Try/Catch to each script is to set XACAT_ABORT ON; then wrap script in begin and commit transaction. Keep in mind this may not catch terminating errors:

http://msdn.microsoft.com/en-us/library/ms188792.aspx

Answer 4

I recently published a new module for interacting with databases that supports transactions:

Install-Module -Name InvokeQuery

You don't need to explicitly roll back if an error occurs. The ambient transaction that's created with Start-Transaction will take care of that for you.

try {
    $db = "test"
    Start-Transaction

    $sql = "insert into table1 values (NEWID(), 8765, 'transactions!', GETDATE())"
    $rowcount = $sql | Invoke-SqlServerQuery -Database $db -UseTransaction -CUD -Verbose
    Write-Host "Inserted $rowcount rows!"

    $sql = "insert into table1 values (NEWID(), 5555, 'transaction too!', GETDATE())"
    $rowcount = $sql | Invoke-SqlServerQuery -Database $db -UseTransaction -CUD -Verbose
    Write-Host "Inserted $rowcount rows!"

    Complete-Transaction
}
catch {
    ##Transaction will automatically be rolled back....
    Write-Error $_
}

If you want to explicitly roll back within the transaction then throw an error:

throw 'rollback because of reason X!'

More examples: https://github.com/ctigeek/InvokeQueryPowershellModule/blob/master/README.md#transactions

Answer 5

I'd recommend checking out Invoke-SqlCmd2 which takes a parameter for an existing SqlConnection.

Installation:

Install-Module Invoke-SqlCmd2 -Scope CurrentUser

Using it with a transaction would look something like this:

$Connection = [System.Data.SqlClient.SqlConnection]::new($ConnectionString)
$Connection.Open()
$Transaction = $Connection.BeginTransaction()

try {
   Invoke-SqlCmd2 -SqlConnection $Connection -Query "..." -ErrorAction Stop
   Invoke-SqlCmd2 -SqlConnection $Connection -Query "..." -ErrorAction Stop
   $Transaction.Commit()
}
catch {
   $Transaction.Rollback()
}
finally {
   $Connection.Close()
}