Guides

Fix Msg 15002: sp_addlinkedserver cannot be executed within a transaction

By Scott Coffey, Senior Database Developer. Updated 2026-10-15.

You wrap a linked server setup script in BEGIN TRAN so it can roll back cleanly, and SQL Server refuses to run it:

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

This is by design. sp_addlinkedserver changes server-level metadata, and SQL Server blocks it when @@TRANCOUNT is greater than zero. Several other server-level procedures behave the same way, including sp_dropserver in many cases. No setting turns the check off. AI coding agents produce this error often, because "wrap it in a transaction" is a reasonable default that happens to be wrong here.

What causes it

Any open transaction counts, not only one in your script. Common sources:

  • An explicit BEGIN TRAN around the whole setup script.
  • SET IMPLICIT_TRANSACTIONS ON in the session, which some client tools and drivers enable.
  • Calling the setup from a stored procedure that is itself called inside a transaction.
  • A deployment tool that wraps each script in a transaction.

Check with SELECT @@TRANCOUNT; right before the call. If it returns anything other than 0, the call fails.

The fix: no transaction, explicit cleanup instead

Because you cannot roll back the linked server creation, you replace the transaction with three things: an existence check so the script is safe to re-run, a dry-run mode, and a CATCH block that removes only the linked server this run created. The pattern below follows linked-server-add.sql from the pack.

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @DryRun     bit            = 1;
DECLARE @LinkedName sysname        = N'LS_REMOTE';
DECLARE @DataSource nvarchar(4000) = N'remote-host,1433';
DECLARE @Provider   nvarchar(128)  = N'MSOLEDBSQL';

IF @@TRANCOUNT > 0
    THROW 50002, 'Open transaction detected. sp_addlinkedserver cannot run inside a transaction (Msg 15002).', 1;

IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @LinkedName)
BEGIN
    PRINT 'Linked server already exists: ' + @LinkedName + '. No changes made.';
    RETURN;
END;

IF @DryRun = 1
BEGIN
    PRINT 'DRY RUN: would create ' + @LinkedName + ' -> ' + @DataSource + ' via ' + @Provider;
    RETURN;
END;

DECLARE @CreatedHere bit = 0;
BEGIN TRY
    EXEC master.dbo.sp_addlinkedserver @server = @LinkedName, @srvproduct = N'',
         @provider = @Provider, @datasrc = @DataSource;
    SET @CreatedHere = 1;

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = @LinkedName, @useself = N'TRUE', @locallogin = NULL;
    EXEC master.dbo.sp_serveroption @server = @LinkedName, @optname = N'rpc out',         @optvalue = N'false';
    EXEC master.dbo.sp_serveroption @server = @LinkedName, @optname = N'connect timeout', @optvalue = N'10';
    EXEC master.dbo.sp_serveroption @server = @LinkedName, @optname = N'remote proc transaction promotion', @optvalue = N'false';
    PRINT 'Created linked server ' + @LinkedName + '.';
END TRY
BEGIN CATCH
    IF @CreatedHere = 1 AND EXISTS (SELECT 1 FROM sys.servers WHERE name = @LinkedName)
    BEGIN
        PRINT 'Setup failed after creating ' + @LinkedName + '. Removing it: ' + ERROR_MESSAGE();
        EXEC master.dbo.sp_dropserver @server = @LinkedName, @droplogins = 'droplogins';
    END;
    THROW;
END CATCH;

Why this is safer than a transaction would have been

  • Idempotent. Re-running does nothing if the server exists, and prints the reason. It never drops and recreates an existing linked server, which would break anything that depends on it.
  • Scoped cleanup. @CreatedHere guarantees the CATCH block only removes an object created in this run. A pre-existing server with the same name is never touched.
  • Not silent. Every path prints what it did, and the original error is rethrown with THROW.
  • Dry run first. The default @DryRun = 1 shows what would happen without changing anything.

Notes on providers and options

Use MSOLEDBSQL; SQLNCLI is deprecated. On SQL Server 2022 and later, MSOLEDBSQL version 19 encrypts connections by default, so either install a certificate the local server trusts or set @provstr deliberately. Keep rpc out off unless you need EXEC ... AT. Never put a remote password in the script; use @useself or supply it at run time through a sqlcmd variable.

If a tool wraps every script in a transaction

Run the linked server script as a separate step outside the wrapper, for example with sqlcmd -i, or move it into a deployment step that is marked non-transactional. Do not try to commit the outer transaction from inside the script; that leaves the tool in an inconsistent state.

After creation, test the connection with EXEC sp_testlinkedserver N'LS_REMOTE'; and a simple OPENQUERY before any job depends on it.