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?

PowerShell has Start-Transaction, is that what you are referring to? http://technet.microsoft.com/en-us/library/hh849772.aspx — , Dec 20 '12 at 17:49
@ShawnMelton No, I tried `Start-Transaction; Invoke-Sqlcmd 'select 1 a into ##test' -ServerInstance myserver; Undo-Transaction` and it still created the table. — ca9163d9, Dec 20 '12 at 18:06

score 18 · Answer 1 · answered Jan 13 '15 at 06:53

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.

this doesnt work so well with the latest powershell version – Alex Gordon Oct 27 '21 at 21:20 — Alex Gordon, Oct 27 '21 at 21:20

score 5 · Answer 2 · edited May 23 '17 at 12:07

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

score 4 · Accepted Answer · edited Nov 10 '17 at 15:17

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

score 2 · Answer 4 · answered May 10 '16 at 21:13

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

Unfortunately, `InvokeQuery` contains bugs and is not maintained anymore. — Monsignor, Feb 07 '20 at 14:34
@Monsignor You are correct. I'm in the process of a ground-up re-write for Powershell 7. — Steven Swenson, Dec 08 '20 at 19:02

score 0 · Answer 5 · answered Nov 01 '21 at 16:37

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()
}

Multiple Invoke-SqlCmd and Sql Server transaction

5 Answers5

Linked