是否可以在跨链接服务器的SQLCLR触发器之间共享本地临时表?

我需要为第三方产品实现分布式事务。我有两个SQL服务器和两个SQLCLR触发器。我想从另一个实例上的第二个触发器上下文访问本地临时表值。这可能吗?

//Server 1
[Microsoft.SqlServer.Server.SqlTrigger (Name="SqlTrigger1", Target="Table1", Event="FOR INSERT")]
 public static void SqlTrigger1 ()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            // Create #Temp table
            // Insert some data             
            // Fire trigger Server 2 via Dblink
         }
    }

//Server 2
[Microsoft.SqlServer.Server.SqlTrigger (Name="SqlTrigger1", Target="Table1", Event="FOR INSERT")]
 public static void SqlTrigger2 ()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            Read #Temp table  ???
         }
    }

直接的答案与SQLCLR无关。跨实例访问本地临时表(或存储过程)在概念上甚至是不可能的，因为与任何其他对象一样，它们对于创建它们的实例来说是本地的。当使用链接服务器时，没有办法访问调用会话，因此服务器1上对本地临时表的引用永远不会被服务器2上运行的代码访问。

另外，虽然至少可以在实例之间访问全局临时表(因为这些对所有会话都是可见的)，但这仍然需要在Server 2上创建一个指向Server 1的额外链接服务器，因为全局临时表将存在于服务器1上。这有点混乱，而且与创建一个真正的表相比没有任何优势(除非您创建全局临时表以包含新创建的GUID值作为其名称的一部分，但是您仍然需要将该值传输到Server 2，以便构建对Server 1的正确引用，这将需要在动态SQL中发生)。

来自op的澄清:

当用户调用查询insert into dbo.Account (Name) values('something')时，我用clr触发器拦截它，并在server2 insert into Server2.dbo.Account (Name) values('something')上执行相同的查询，我需要在此事务中共享上下文，例如guid变量。

实例之间没有所谓的"shard context"。两个地方需要的任何数据和/或值都需要传递到远程实例中。在这种情况下，您可以在NVARCHAR(MAX)变量中将数据打包为XML，并在Server 2上执行一个存储过程，传入该NVARCHAR(MAX)值，在存储过程中将其转换为XML，然后使用.nodes()将其解包。然后，还可以将单个标量值作为其他参数传递给远程存储过程。例如:

DECLARE @DataToTransfer NVARCHAR(MAX),
        @SomeGuid UNIQUEIDENTIFIER;
SET @DataToTransfer = (
    SELECT *
    FROM   inserted
    FOR XML RAW('row')
  );
EXEC [LinkedServerName].[DatabaseName].[SchemaName].[StoredProcedureName]
    @Param1 = @DataToTransfer,
    @Param2 = @SomeGuid;

上面显示的方法工作得很好。我使用它每天将数百万行数据从18个生产服务器传输到单个归档服务器。与在链接服务器上直接执行DML/INSERT语句相比，调用远程存储过程的锁问题更少。此外，这种方法允许发送数据表(打包为XML)和单个变量值(例如您提到的Guid)。

远程存储过程——在上面示例代码中的EXEC中引用——将在Server 2上本地执行，因此它可以创建一个本地临时表，远程表上的触发器将可以访问，或者使用SET CONTEXT_INFO，或者如果使用SQL Server 2016(或更新版本)，使用sp_set_session_context。

而且，正如您可能已经注意到的，这些都与SQLCLR没有任何关系。当您将无法使用SQLCLR触发器/对象的任何优点时，我认为没有理由在SQLCLR中引入额外的复杂性。

使用本地临时表不可以，但是使用全局临时表(两个磅号:##globalTemp而不是#temp)应该可以。话虽如此，这可能不是一个好主意，因为您永远不知道##globalTemp表是否存在。谁应该创造它?

有两种类型的临时表:本地和全局。当地的临时表只对其创建者可见连接到一个SQL Server实例，就像第一次创建表时一样已创建或引用。对象之后删除本地临时表用户从SQL Server实例断开连接。全局临时表对任何用户和连接都是可见的创建，并在所有用户都引用该表时删除从SQL Server实例断开连接。

相关内容

最新更新

热门标签：