在存储过程事务错误中执行 addlinkedServer

问题描述：

我正在尝试在 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

相关推荐