sql-server - 你如何在 T-SQL 的新事务中写入表

Question

我们需要在我们的存储过程中将消息记录到数据库中——基本上是写入一个表。但是，如果事务需要回滚，我们会丢失这些日志。

有没有办法在新的或不同的事务中写入这些日志(表插入)以确保它们被持久化，而不管当前存储过程的事务如何？

Answer 1

谢谢安德鲁 - 这是您链接的 MS 示例的简化版本。

要点是创建链接服务器:

'EXEC sp_addlinkedserver @server = 'linkbackServer,@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME ' 

有两个选项

不要使用父事务

'EXEC sp_serveroption @serverName,N'remote proc transaction promotion','FALSE' 

允许远程过程调用

'EXEC sp_serveroption @serverName,N'RPC OUT','TRUE' 

这是创建和测试的所有代码

-- Create the linked server

    use tempdb
    declare @serverName varchar(10) = N'loopback'
    if exists (select * from sys.servers where is_linked = 1 and name = @serverName) begin
       exec sp_dropserver @server=@serverName
    end

    EXEC sp_addlinkedserver @server = @serverName,@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME 
    EXEC sp_serveroption @serverName,N'remote proc transaction promotion','FALSE' 
    EXEC sp_serveroption @serverName,N'RPC OUT','TRUE' 


    -- Create the logging and testing tables
    if exists (select * from sys.all_objects where name = 'ErrorLogging' and type = N'U') begin
        DROP TABLE ErrorLogging
    END
    CREATE TABLE ErrorLogging (logTime DATETIME, id int, msg VARCHAR(255)) 

    if exists (select * from sys.all_objects where name = 'TestAT' and type = N'U') begin
       DROP TABLE TestAT
    END
    CREATE TABLE TestAT (id INT PRIMARY KEY) 
    GO

    -- create the logger stored proc
    if exists (select * from sys.all_objects where name = 'MyLogger' and type = N'P') begin
      DROP PROCEDURE [dbo].[MyLogger]
    END
    GO

    CREATE PROCEDURE MyLogger 
       @errNumber INT,
       @errMessage varchar(50)
    AS BEGIN
          INSERT INTO ErrorLogging VALUES 
            (GETDATE(), @errNumber, @errMessage ) 
    END
    GO

    -- test the code
    USE tempdb
    delete from dbo.TestAT;
    delete from errorLogging;

    BEGIN TRAN 
      INSERT INTO TestAT VALUES (1) 
      EXEC [loopback].[tempdb].dbo.MyLogger @errNumber = 42, @errMessage = N'this will be saved'
      EXEC [tempdb].dbo.MyLogger @errNumber = 66, @errMessage = N'this will NOT be saved'
    ROLLBACK 

    SELECT * FROM TestAT 
    SELECT * FROM ErrorLogging GO