CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user3428422
user3428422

Reputation: 4560

Executing addlinkedServer within a Stored Procedure Transaction Error

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

Upvotes: 2

Views: 4493

Answers (2)

user3428422
user3428422

Reputation: 4560

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

Upvotes: 3

Tanner
Tanner

Reputation: 22733

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

Upvotes: 2

Related Questions