多个Invoke-SqlCmd和Sql Server事务

Question

如何确保以下语句在一个事务中运行？

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;'
}

在事务中运行Powershell脚本的最佳方法是什么？

Answer 1

Invoke-sqlcmd不支持ADO.NET转换。您有两种解决方法，或者编写与ADO.NET事务的MSDN文档中所示的C#代码等效的Powershell代码：http://msdn.microsoft.com/en-us/library/2k2hy99x(v=vs.110).aspx

或者使用T-SQL事务。不向每个脚本添加T-SQL /Catch的快速方法是将XACAT_ABORT设置为ON；然后将脚本包装在begin和commit transaction中。请记住，这可能无法捕获终止错误：

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

Answer 2

不幸的是，Powershell中的SqlProvider没有像Registry提供程序那样的事务处理能力。因此，Start-transaction不能在这里工作。

您可以原生使用TransactionScope类。

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

放在$scope赋值和$scope.complete()之间的所有Invoke-sqlcmd都将被视为一个事务。如果它们中的任何一个出错，所有的错误都将被回滚。

Answer 3

我最近发布了一个新的模块，用于与支持事务的数据库交互：

Install-Module -Name InvokeQuery

如果发生错误，您不需要显式回滚。使用Start-Transaction创建的环境事务将为您处理此问题。

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 $_
}

如果你想在事务中显式回滚，那么抛出一个错误：

throw 'rollback because of reason X!'

更多示例：https://github.com/ctigeek/InvokeQueryPowershellModule/blob/master/README.md#transactions