如何在PowerShell中提交SQL事务？执行COMMIT遇Msg3902报错求解

问题原因 #

每次调用Invoke-Sqlcmd都会创建一个全新的数据库连接会话：你第一次执行带注释COMMIT的SQL脚本时，事务仅存在于那个会话中；当你单独调用Invoke-Sqlcmd -Query "commit"时，是在新会话里执行，这个会话里没有对应的BEGIN TRANSACTION，因此会触发报错。

解决方案 #

方案一：在同一会话中执行脚本并提交事务 #

把SQL脚本内容和COMMIT语句合并，在同一个Invoke-Sqlcmd调用中执行，确保事务与提交操作处于同一会话：

Write-Host "Running script"

$SQLServer4 = "ServerA" 
$Database4 = 'master'
$scriptPath = "C:\Users\Documents\Scripts\SaveSite.txt"

# 读取脚本内容并追加COMMIT语句
$fullSql = Get-Content $scriptPath -Raw
$fullSql += "`nCOMMIT;"

Invoke-Sqlcmd -ServerInstance $SQLServer4 -Database $Database4 -Query $fullSql

方案二：使用.NET SqlClient保持连接，手动控制事务 #

这种方式可以在同一个连接会话中完成脚本执行、结果确认、事务提交的全流程，适合需要先验证执行结果再决定是否提交的场景：

Write-Host "Running script"

$SQLServer4 = "ServerA" 
$Database4 = 'master'
$scriptPath = "C:\Users\Documents\Scripts\SaveSite.txt"

# 构建连接字符串（SQL认证需添加UID和PWD参数）
$connectionString = "Server=$SQLServer4;Database=$Database4;Integrated Security=True;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()

try {
    # 执行SQL脚本（脚本需包含BEGIN TRANSACTION和业务操作，COMMIT已注释）
    $command = $connection.CreateCommand()
    $command.CommandText = Get-Content $scriptPath -Raw
    $affectedRows = $command.ExecuteNonQuery()

    # 确认执行结果（可根据业务需求自定义验证逻辑）
    Write-Host "脚本执行完成，影响行数: $affectedRows"
    Write-Host "确认无误，执行COMMIT..."

    # 在同连接中提交事务
    $commitCommand = $connection.CreateCommand()
    $commitCommand.CommandText = "COMMIT;"
    $commitCommand.ExecuteNonQuery()

    Write-Host "事务已成功提交"
}
catch {
    Write-Error "执行出错: $_"
    # 出错时回滚事务
    $rollbackCommand = $connection.CreateCommand()
    $rollbackCommand.CommandText = "ROLLBACK;"
    $rollbackCommand.ExecuteNonQuery()
    Write-Host "事务已回滚"
}
finally {
    # 关闭并释放连接资源
    $connection.Close()
    $connection.Dispose()
}

方案三：修改SQL脚本，通过参数控制提交 #

在SQL脚本中加入条件提交逻辑，通过PowerShell传入参数控制是否执行COMMIT：

修改后的SQL脚本（SaveSite.txt）

BEGIN TRANSACTION;

-- 你的业务操作语句
-- 示例：UPDATE YourTable SET ColumnName = 'NewValue' WHERE Id = 1;

-- 定义参数控制提交行为
DECLARE @IsCommit BIT = 0;
IF @IsCommit = 1
BEGIN
    COMMIT TRANSACTION;
    PRINT '事务已提交';
END
ELSE
BEGIN
    PRINT '事务未提交，可手动执行COMMIT';
    -- 可选：不需要保留事务时可添加回滚
    -- ROLLBACK TRANSACTION;
END

PowerShell执行代码

Write-Host "Running script"

$SQLServer4 = "ServerA" 
$Database4 = 'master'
$scriptPath = "C:\Users\Documents\Scripts\SaveSite.txt"

# 传入参数IsCommit=1触发事务提交
Invoke-Sqlcmd -ServerInstance $SQLServer4 -Database $Database4 -InputFile $scriptPath -Variable "IsCommit=1"

内容的提问来源于stack exchange，提问作者movement