.NET TransactionScopeOption.Suppress 的 T-SQL 等效项 [英] T-SQL Equivalent of .NET TransactionScopeOption.Suppress

问题描述

在我的 .NET 代码中，在数据库事务中(使用 TransactionScope)，我可以包含一个带有 TransactionScopeOption.Suppress 的嵌套块，这确保即使外部块回滚，嵌套块内的命令也会被提交.以下是代码示例:

In my .NET code, inside a database transaction (using TransactionScope), I could include a nested block with TransactionScopeOption.Suppress, which ensures that the commands inside the nested block are committed even if the outer block rolls back. Following is a code sample:

using (TransactionScope txnScope = new TransactionScope(TransactionScopeOption.Required))
{
    db.ExecuteNonQuery(CommandType.Text, "Insert Into Business(Value) Values('Some Value')");

    using (TransactionScope txnLogging = new TransactionScope(TransactionScopeOption.Suppress))
    {
        db.ExecuteNonQuery(CommandType.Text, "Insert Into Logging(LogMsg) Values('Log Message')");
        txnLogging.Complete();
    }

    // Something goes wrong here. Logging is still committed

    txnScope.Complete();
}

我试图找到这是否可以在 T-SQL 中完成.有几个人推荐了 OPENROWSET，但它看起来不太优雅'使用.此外，我认为将连接信息放在 T-SQL 代码中是一个坏主意.

I was trying to find if this could be done in T-SQL. A few people have recommended OPENROWSET, but it doesn't look very 'elegant' to use. Besides, I think it is a bad idea to put connection information in T-SQL code.

我过去使用过 SQL Service Broker，但它也支持事务性消息传递，这意味着在提交数据库事务之前不会将消息发布到队列中.

I've used SQL Service Broker in past, but it also supports Transactional Messaging, which means message is not posted to the queue until the database transaction is committed.

我的要求:我们的应用程序存储过程在存储过程之外启动的隐式事务中被某些第三方应用程序触发.我希望能够在我的存储过程中捕获和记录任何错误(在同一数据库的数据库表中).我需要重新抛出异常，让第三方应用回滚事务，并让它知道操作失败(从而在失败时执行所需的任何操作).

My requirement: Our application stored procedures are being fired by some third party application, within an implicit transaction initiated outside stored procedure. And I want to be able to catch and log any errors (in a database table in the same database) within my stored procedures. I need to re-throw the exception to let the third party app rollback the transaction, and for it to know that the operation has failed (and thus do whatever is required in case of a failure).

推荐答案

您可以设置环回链接服务器，将 remote proc transaction Promotion 选项设置为 false，然后在 TSQL 中访问它或使用SQL Server 中的 CLR 过程，用于在事务外部创建新连接并完成您的工作.

You can set up a loopback linked server with the remote proc transaction Promotion option set to false and then access it in TSQL or use a CLR procedure in SQL server to create a new connection outside the transaction and do your work.

如何在 SQL Server 2008 中创建自治事务.

这两种方法都涉及创建新连接.有一个 打开连接项请求原生提供此功能.

Both methods involve creating new connections. There is an open connect item requesting this functionality be provided natively.

这篇关于.NET TransactionScopeOption.Suppress 的 T-SQL 等效项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！