在存储过程事务错误中执行 addlinkedServer [英] Executing addlinkedServer within a Stored Procedure Transaction Error

问题描述

我正在尝试在 T-SQL 的存储过程中执行链接服务器.

I am trying to execute a link server within a stored procedure in T-SQL.

但是，我收到错误

无法在事务中执行过程sys.sp_addlinkedserver".

The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.

这似乎很明显.但是之后我需要使用动态 SQL 来执行 2 个查询，但是我试图在执行查询之前添加链接服务器，所以不确定为什么会出现错误.

This seems obvious enough. But I need to use dynamic SQL to execute 2 queries afterwards, however I am trying to add the link server before executing the queries, so not sure why the error appears.

这是代码.

  @sqlDBName VARCHAR(MAX),
  @sqlServerName VARCHAR(MAX)
  AS

  EXEC sp_addlinkedserver 
  @sqlServerName,
  N'SQL Server';


  DECLARE @impUpd VARCHAR(MAX)

  SET @impUpd = '

  UPDATE Customers SET NAME = 'Name'
  FROM Customers c
  JOIN ['+@sqlServerName +'].['+@sqlDBName +'].[dbo].[CUSTOMERS2] 
  ..etc


  exec(@impUpd)

任何帮助都会很好.

谢谢

推荐答案

好的，尽管 Tanner 的代码是正确的并且有效，但它并没有真正回答我问的问题..或任何您想做的事情.)但是，我找到了解决我的问题的方法.重复一遍，无法在事务中执行过程‘sys.sp_addlinkedserver’"

OK, even though Tanner's code is correct and works, it didn't really answer the question I asked.. (Yeah OK..my bad (down vote or whatever you want to do.) However, I found the solution to my problem. To repeat, it was "The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction"

这与 SQL 无关，而是与从 C# 和 Entity Freamework 执行存储过程有关.

it wasn't anything to do with SQL, it was to do with executing the Stored Procedure from C# and Entity Freamework.

当我用

m_Context.Database.ExecuteSqlCommand("usp_storedprocedureName");

它和它一起工作

m_Context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction,"usp_storedprocedureName");

如果不研究额外参数的作用，我无法提供它起作用的原因(没有枚举的内容！)，但此处提供了更多信息.

Without researching what the extra parameter does, I can't provide a reason why it worked (without being obvious by what the enum says!), however more information is available here.

已存储在 SQL Azure 上调用 exec sp 并使用 EF6 抛出时的过程

我希望这会有所帮助.

这篇关于在存储过程事务错误中执行 addlinkedServer的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！