Executing addlinkedServer within a Stored Procedure Transaction Error

Question

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

However, I am getting the error

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

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.

Here is the code.

  @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)

Any help would be good.

Thanks

what's the requirement to create a link server? will you create one every time you run this stored proc? why aren't they just created up front? — Tanner, May 19 '14 at 09:11
It wont be often, I just want to put it so if we ever put the application on a new server, the code is there to do it. However, if the solution is more complex or like a hack then it can be put out — user3428422, May 19 '14 at 09:15

score 3 · Answer 1 · edited Oct 19 '22 at 01:26

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

The error happened when I called the SP with

m_Context.Database.ExecuteSqlCommand("usp_storedprocedureName");

And it worked with

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.

Stored procedure working when calling exec sp and throws with EF6 on SQL Azure

score 2 · Answer 2 · edited May 23 '17 at 11:45

If you are required to create a linked server on the fly, as opposed to manually creating it up front, then do it in it's own stored procedure that only does that so you have "Separation of Concerns".

You don't want to start adding that linked server creation into stored procedures that do other things, otherwise it will happen in many places.

So within your application, either at start up or some point in the code execution where it's sensible, call a stored procedure to check if the linked server exists, and create it if it doesn't. Then from that point you can be sure it is present.

See this post for an example of code to create a linked server if it doesn't exist:

Sample

DECLARE @sqlServerName VARCHAR(MAX)

IF NOT EXISTS (SELECT 1 from sys.servers where name = @sqlServerName)
BEGIN
  PRINT 'Does not exist, need to create link'
  EXEC sp_addlinkedserver @server = @sqlServerName, @srvproduct=N'SQL Server'
END

Thank you Tanner, that was going to be my next thought, check to see if the link is there, if so, link it, if not, ignore. I just wasn't sure if you could run a link server in dynamic SQL first. Thanks — user3428422, May 19 '14 at 09:31

Executing addlinkedServer within a Stored Procedure Transaction Error

2 Answers2

Linked