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.