当前位置：文江博客话题详情

从另一台服务器（链接服务器）获取@@Identity

发布于 2024-10-09 10:36:57 字数 88 浏览 6 评论 0原文

我有一个链接服务器，我想向链接服务器上的表添加一条记录，是否可以从具有链接服务器的另一台服务器获取@@identity？（SQL Server 2005）

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

想挽留 2024-10-16 10:36:58

此处使用 SQL Server 2012。

我尝试过 gbn 方法，但收到此错误：

Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

除此之外，我需要在语句之前添加 SET XACT_ABORT ON; ，因为我正在使用分布式事务。

因此，我最终通过这样做解决了这两个问题：

BEGIN DISTRIBUTED TRANSACTION

SELECT idcolumn
FROM OPENQUERY(MyRemoteServer, '
  SET XACT_ABORT ON;
  INSERT INTO MyRemoteBD.dbo.SomeTable(col1,col2, ...) 
  OUTPUT INSERTED.idcolumn
  VALUES (val1,val2, ...);
  SET XACT_ABORT OFF');

COMMIT

并且我能够在分布式事务中运行它，同时从插入中获取身份。

关于此语句的一个奇怪的行为是，如果我在之前没有打开分布式事务（必须是分布式事务）的情况下运行插入语句，它将返回下一个标识，但不会插入任何内容。我不知道为什么会发生这种情况。

编辑：

尝试了其他方法，它不需要在分布式事务上设置 XACT_ABORT ON 即可工作，并且无需事务即可正常工作。

exec [MyRemoteServer].MyRemoteBD.dbo.sp_executesql N'
    INSERT INTO SomeTable (col1,col2,...) 
    VALUES (val1,val2,...); 
    SELECT SCOPE_IDENTITY ()'

Using SQL Server 2012 here.

I've tried gbn method but I got this error:

Msg 405, Level 16, State 1, Line 1
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.

Besides that, I needed to add the SET XACT_ABORT ON; before my statement as I was using Distributed Transactions.

So I ended up solving both the issues by doing this:

BEGIN DISTRIBUTED TRANSACTION

SELECT idcolumn
FROM OPENQUERY(MyRemoteServer, '
  SET XACT_ABORT ON;
  INSERT INTO MyRemoteBD.dbo.SomeTable(col1,col2, ...) 
  OUTPUT INSERTED.idcolumn
  VALUES (val1,val2, ...);
  SET XACT_ABORT OFF');

COMMIT

And I was able to run this in a Distributed Transaction while getting the Identity from my insert.

A weird behavior about this statement is that if I run the Insert statement without opening the Distributed Transaction previously (must be the distributed one) it will return the next Identity but it will not insert anything. I have no idea why this is happening.

Edit:

Tried this other method and it has worked without the need of setting XACT_ABORT ON on distributed transactions and works fine without transactions.

exec [MyRemoteServer].MyRemoteBD.dbo.sp_executesql N'
    INSERT INTO SomeTable (col1,col2,...) 
    VALUES (val1,val2,...); 
    SELECT SCOPE_IDENTITY ()'

回复收藏 0 原文