sql - 使用 powershell 为单个事务中的多个文件调用 sqlcmd

Question

在网上找到大量如何在 powershell 中执行以下操作的示例:

Get-ChildItem $databaseFolder -Filter *.sql -Recurse | ForEach-Object { sqlcmd -S  $databaseServer -d $databaseName -E -i $_.FullName } 

但是，我想将其分组到一个事务下，这样如果其中一个 sql 文件失败，那么所有内容都会回滚。

我也在尝试的另一种方法是将所有文件内容放在一个完整的脚本变量中:

foreach( $file in Get-ChildItem -path $databaseFolder -Filter *.sql | sort-object )
{
    Get-Content $file.fullName | Foreach-Object { $fullSqlScript = $fullSqlScript + $_ + "`n" } ;
}

最后像这样执行:

 invoke-sqlcmd -ServerInstance $databaseServer -Database $databaseName -Query $fullSqlScript | format-table | out-file -filePath $outFile

$fullSqlScript 还将在顶部插入以下内容:

:On Error Exit
 SET XACT_ABORT ON
 GO
 Begin Transaction

并结束:

IF XACT_STATE() = 1
BEGIN
    PRINT 'Committing Transaction...'
    COMMIT TRANSACTION
END
ELSE IF XACT_STATE() = -1
BEGIN
    PRINT 'Scripts Failed... Rolling back'
    ROLLBACK TRAN
END

但是，一旦我故意使 sql 脚本失败，整个数据库就会被锁定，就像事务无法回滚一样。我假设这与在 powershell 脚本环境中使用 invoke-sqlcmd 而不是来自 Windows 命令提示符的 sqlcmd 有关？

Answer 1

我相信它已经解决了。看完以下article关于让 On Error Exit 只能使用 sqlcmd 执行的能力，我肯定对 powershell invoke-sqlcmd 有疑问。

这是我在这里找到的一些文档，我认为可能与此有关: http://technet.microsoft.com/en-us/library/cc281720.aspx

特别是以下内容:*

Not all of the sqlcmd commands are implemented in Invoke-Sqlcmd. Commands that are not implemented include the following: :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.

*

最后，我仍然使用必须读取每个文件的内容的策略，然后:

:On Error Exit

在最终写入包含其他文件中所有其他 sql 的输出文件的脚本顶部。

然后，我执行以下操作来调用实际的 sqlcmd:

$command = "sqlcmd.exe -S " + $databaseServer + " -d " + $databaseName + " -i " + $outFile
Invoke-Expression $command

这似乎工作得很好，一切都会回滚 :)如果有人能想到更好的解决方案，请告诉我!