在当前事务之外提交事务（如Oracle中的自治事务）

Question

我需要从存储过程写入日志表。 现在这个日志信息必须在回滚场外生存。

我知道这个问题已被问过，但我的情况是不同的，我无法在这些问题中找到我的问题的答案。

当存储过程中没有错误时，事情很简单，日志表中的条目就在那里。
出现错误时比事情复杂。
在过程中，我可以在catch中回滚，然后将数据插入到日志表中，我知道这一点，而且我已经这样做了。
但问题是，当调用存储过程是这样的：

begin transaction 
exec myStoredProcedure 
rollback transaction 
select * from myLogTable 

我知道这个代码使没有太大意义，我一直是mimimal来证明我的问题。
如果存储过程的调用者执行了提交/回滚，那么它对我在存储过程中所做的并不重要。我的logentry将永远回滚。

我也不能使用临时表技巧，它是返回我想要记录的数据，并让调用者使用该数据在完成回滚之后将其插入到日志表中，因为调用者是外部应用程序我没有来源。

日志记录是在一个单独的过程中完成的，只有一行代码插入到日志表中。
我需要的是一种在当前事务之外的过程中提交插入的方式，因此它可以在任何回滚之后存活。

有没有办法做到这一点？

解决方案：

我用lad2025答案，迄今为止，它是工作没有任何问题或性能问题。
但是这个过程每天只会被调用大约1000次，这并不是那么多，所以我想我也不需要任何问题。

Answer 1

这是很有趣的话题让我们检查MS如何处理它。

第一文档：Migrating-Oracle-to-SQL-Server-2014-and-Azure-SQL-DB.pdf

页152

模拟甲骨文自治事务

本节介绍SSMA为Oracle V6.0如何处理自治事务 （PRAGMA AUTONOMOUS_TRANSACTION）。这些自治事务不在 中具有Microsoft SQL Server 2014中的直接等效项。

当您将自定义PL/SQL块您将 与该调用程序的事务上下文中的DML隔离开来。该块变为 由另一个交易开始的独立交易，被称为主要交易 。

了标记PL/SQL块作为一个自治事务，您只需包括 按照你的声明部分声明： PRAGMA AUTONOMOUS_TRANSACTION;

SQL Server 2014不支持自治事务。将Transact-SQL块与事务上下文分离的唯一方法是打开一个新的连接。

使用xp_ora2ms_exec2扩展过程和它的扩展版本 xp_ora2ms_exec2_ex，与SSMA 6.0扩展包捆绑在一起，开辟新的 交易。该过程的目的是调用新连接中的任何存储过程，并帮助调用函数体内的存储过程。该 xp_ora2ms_exec2程序的语法如下：

xp_ora2ms_exec2 
<active_spid> int, 
<login_time> datetime, 
<ms_db_name> varchar, 
<ms_schema_name> varchar, 
<ms_procedure_name> varchar, 
<bind_to_transaction_flag> varchar, 
[optional_parameters_for_procedure]; 

然后，你需要安装在你的服务器的存储过程和其他脚本： SSMA for Oracle Extension Pack（仅SSMA用于Oracle扩展Pack.7.5.0.msi）。

你的存储过程将变成：

CREATE TABLE myLogTable(i INT IDENTITY(1,1), 
         d DATETIME DEFAULT GETDATE(), 
         t NVARCHAR(1000)); 
GO 

CREATE OR ALTER PROCEDURE my_logging 
    @t NVARCHAR(MAX) 
AS 
BEGIN 
    INSERT INTO myLogTable(t) VALUES (@t); 
END; 
GO 

CREATE OR ALTER PROCEDURE myStoredProcedure 
AS 
BEGIN 
    -- some work 
    SELECT 1; 
    INSERT INTO myLogTable(t) 
    VALUES ('Standard logging that will perish after rollback'); 

    DECLARE @login_time DATETIME = GETDATE(); 
    DECLARE @custom_text_to_log NVARCHAR(100); 
    SET @custom_text_to_log=N'some custom loging that should survive rollback'; 
    DECLARE @database_name SYSNAME = DB_NAME(); 

    EXEC master.dbo.xp_ora2ms_exec2_ex 
     @@spid, 
     @login_time, 
     @database_name, 
     'dbo', 
     'my_logging', 
     'N', 
     @custom_text_to_log; 
END; 

和最终呼叫：

begin transaction 
exec myStoredProcedure 
rollback transaction 
select * from myLogTable; 

OUTPUT：

i d   t 
2 2017-08-21 some custom loging that should survive rollback 

Answer 2

所以你真的搜索某种Autonomous transaction（就像在Oracle中）。

模拟它的一个难看的方法是使用环回链接服务器。

警告：这是PoC（在我将它用于PROD之前我会考虑两次）并进行大量测试。

DECLARE @servername SYSNAME; 
SET  @servername = CONVERT(SYSNAME, SERVERPROPERTY(N'ServerName')); 

EXECUTE sys.sp_addlinkedserver 
      @server = N'loopback', 
      @srvproduct = N'', 
      @provider = N'SQLNCLI', 
      @datasrc = @servername; 

EXECUTE sys.sp_serveroption 
      @server = N'loopback', 
      @optname = 'RPC OUT', 
      @optvalue = 'ON'; 

EXECUTE sys.sp_serveroption 
     @server = N'loopback', 
     @optname = 'remote proc transaction promotion', 
     @optvalue = 'OFF'; 

和代码：

DROP TABLE IF EXISTS myLogTable; 
CREATE TABLE myLogTable(i INT IDENTITY(1,1), 
         d DATETIME DEFAULT GETDATE(), 
         t NVARCHAR(1000)); 
GO 

CREATE OR ALTER PROCEDURE my_logging 
    @t NVARCHAR(MAX) 
AS 
BEGIN 
    INSERT INTO myLogTable(t) VALUES (@t); 
END; 
GO 

CREATE OR ALTER PROCEDURE myStoredProcedure 
AS 
BEGIN 
    -- some work 
    SELECT 1; 
    INSERT INTO myLogTable(t) 
    VALUES ('Standard logging that will perish after rollback'); 

    EXEC loopback.T1.dbo.my_logging 
     @t = N'some custom loging that should survive rollback'; 
END; 

最终调用：

begin transaction 
exec myStoredProcedure 
rollback transaction 
select * from myLogTable 

输出：

i d   t 
2 2017-08-17 some custom loging that should survive rollback