0

I have the following stored procedure:

CREATE Procedure [dbo].[sc_Server_Link]
    @ServerName varchar(255),
    @ServerSource varchar(255),
    @DatabaseName varchar(255),
    @UserName varchar(255),
    @UserPass varchar(255)
AS
BEGIN
    IF NOT EXISTS ( SELECT 1 FROM sys.sysservers WHERE srvname = @ServerName )
    BEGIN
        EXEC sp_addlinkedserver
                @server = @ServerName,
                @srvproduct = '',
                @provider = 'sqlncli',
                @datasrc = @ServerSource,
                @location = '',
                @provstr = '',
                @catalog = @DatabaseName

        --Add credentials and options to this linked server
        IF NOT EXISTS ( SELECT 1 FROM sys.linked_logins WHERE remote_name = @UserName)
            EXEC sp_addlinkedsrvlogin
                @rmtsrvname = @ServerName,
                @useself = 'false',
                @rmtuser = @UserName,
                @rmtpassword = @UserPass

        --Set Options
        EXEC sp_serveroption @ServerName, 'rpc out', true;
    END
END

Executing the procedure works fine but afterwards, every time I try to do a transaction... the transaction works but I get the following errors:

Msg 15002, Level 16, State 1, Procedure sp_addlinkedserver, Line 71
The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.

Msg 15002, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 35
The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.

Msg 15002, Level 16, State 1, Procedure sp_serveroption, Line 37
The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.

  • How can I stop these errors?
  • How can I correct my stored procedure so this won't happen again?

Thanks for your help!

Community
  • 1
  • 1
Nitramuteh
  • 1
  • 1
  • This answer might help: http://stackoverflow.com/a/23734809/3393505 – Siyual Dec 16 '15 at 16:15
  • 2
    Doesn't the error message tell you all you need to know already??? – TT. Dec 16 '15 at 16:33
  • Even if I stop using this sp... I'm still stuck with those 3 errors every insert/update/delete I do in my local database – Nitramuteh Dec 16 '15 at 16:42
  • Close the transaction if you still have your session open. Otherwise close your session. Just a guess. – TT. Dec 16 '15 at 16:44
  • If that doesn't help (or you can't figure out how to), restart the SQL Server database engine (how to [here](https://msdn.microsoft.com/en-us/library/hh403394.aspx)) – TT. Dec 16 '15 at 16:48

0 Answers0