Explicit sql transaction in powershell
There are some hoops you need to jump through to get a multi-statement transaction committed via powershell. Default time-out is set to only one minute, so using the code below you can increase it. $timeout = New-Object System.TimeSpan -ArgumentList @ (5,0,0) # five hours $options = [System.Transactions.TransactionScopeOption] :: Required $scope = New-Object -TypeName System.Transactions.TransactionScope -ArgumentList @ ( $options , $timeout ) try { #Multiple queries, loops or whatever fun here, inside transaction Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName1 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName2 ; Invoke-Sqlcmd -ErrorAction 'Stop' -querytimeout 0 -ServerInstance $server -Database $database -InputFile $fileName3 ; $scope .Complete() #we tell transactionscope to commit when using e...