Dynamic Linked Server Management in SQL Server

Introduction In SQL Server, sometimes we need to connect with another database server. This is done using a Linked Server . But manually creating and deletin...

Mar 30, 2026 9 min read

Introduction

In SQL Server, sometimes we need to connect with another database server. This is done using a Linked Server.

But manually creating and deleting linked servers again and again is not efficient.

In this blog, we will understand how to automate this process using a stored procedure:

What is a Linked Server?

A Linked Server allows SQL Server to connect with another database server and run queries.

👉 Example:
You can run query like:

SELECT * FROM [LinkedServer].[Database].[dbo].[Table]

What Problem Are We Solving?

Normally:

You manually create linked server
Add login
Configure settings
Drop it later

This is time-consuming and error-prone.

Solution:

We use a Stored Procedure that:

Creates linked server
Drops linked server
Handles errors
Uses configuration dynamically

Overview of the Stored Procedure

Procedure Name:

usp_LinkedServiceManager

It supports two actions:

@Action = 1 → Create Linked Server
@Action = 2 → Drop Linked Server

IF OBJECT_ID('usp_LinkedServiceManager', 'P') IS NOT NULL
    DROP PROCEDURE usp_LinkedServiceManager;
GO

/*
================================================================================
PROCEDURE  : usp_LinkedServiceManager
PURPOSE    : Dynamically creates or drops Linked Servers for DB Archive job.
             Remote LS is created from ScheduledTaskSettings config.
             Local LS is optional (controlled by @IsCreateLocalLS).

PARAMETERS :
  @Action             INT          1 = CREATE linked server(s)
                                   2 = DROP  linked server(s)
  @Password           NVARCHAR     Remote server login password
  @IsCreateLocalLS    BIT          1 = Also create a local loopback Linked Server
  @LocalPassword      NVARCHAR     Local LS login password (required if @IsCreateLocalLS=1)
  @OutLinkedServer    OUTPUT       Name of the remote Linked Server created
  @OutLinkedServerDB  OUTPUT       4-part DB prefix for remote LS
  @OutLocalLS         OUTPUT       Name of the local Linked Server created
  @OutLocalLSDB       OUTPUT       4-part DB prefix for local LS
  @OutReturnCode      OUTPUT       0 = Success | 1 = Validation Error | 2 = Runtime Error
  @OutErrorMessage    OUTPUT       Human-readable error detail (empty on success)

RETURN CODES :
  0  = Success
  1  = Validation failed (bad input, missing config)
  2  = Runtime error (linked server create/drop failed)

USAGE (standalone) :
  DECLARE @RC INT, @Err NVARCHAR(1000);
  EXEC usp_LinkedServiceManager
      @Action=1, @Password='Xyz#123',
      @OutReturnCode=@RC OUTPUT, @OutErrorMessage=@Err OUTPUT;
  IF @RC <> 0 PRINT @Err;

USAGE (inner call from another SP) :
  EXEC usp_LinkedServiceManager
      @Action=1, @Password='Xyz#123',
      @OutReturnCode=@RC OUTPUT, @OutErrorMessage=@Err OUTPUT;
  IF @RC <> 0
  BEGIN
      -- handle or re-raise as needed in outer SP
      RAISERROR(@Err, 16, 1);
      RETURN;
  END


================================================================================
*/

CREATE PROCEDURE usp_LinkedServiceManager
(
    @Action             INT           = 1,
    @Password           NVARCHAR(100) = '',
    @IsCreateLocalLS    BIT           = 0,
    @LocalPassword      NVARCHAR(100) = '',
    @OutLinkedServer    NVARCHAR(800) = NULL OUTPUT,
    @OutLinkedServerDB  NVARCHAR(800) = NULL OUTPUT,
    @OutLocalLS         NVARCHAR(800) = NULL OUTPUT,
    @OutLocalLSDB       NVARCHAR(800) = NULL OUTPUT,
    @OutReturnCode      INT           = 0    OUTPUT,   -- 0=Success, 1=Validation, 2=Runtime
    @OutErrorMessage    NVARCHAR(2000)= ''   OUTPUT    -- Empty on success
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Initialize output params
    SET @OutReturnCode    = 0;
    SET @OutErrorMessage  = '';
    SET @OutLinkedServer  = NULL;
    SET @OutLinkedServerDB= NULL;
    SET @OutLocalLS       = NULL;
    SET @OutLocalLSDB     = NULL;

    -- =========================================================================
    -- SECTION 1 : Input Validation
    -- =========================================================================
    IF @Action NOT IN (1, 2)
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] Invalid @Action value. Use 1=CREATE or 2=DROP.';
        RETURN;
    END

    IF @Action = 1 AND (@Password IS NULL OR LTRIM(RTRIM(@Password)) = '')
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] @Password is required when @Action=1 (CREATE).';
        RETURN;
    END

    IF @Action = 1 AND @IsCreateLocalLS = 1
       AND (@LocalPassword IS NULL OR LTRIM(RTRIM(@LocalPassword)) = '')
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] @LocalPassword is required when @IsCreateLocalLS=1.';
        RETURN;
    END

    -- =========================================================================
    -- SECTION 2 : Read Config from ScheduledTaskSettings
    -- =========================================================================
    DECLARE @SchtaskID    INT           = 0,
            @DBServerName NVARCHAR(100) = '',
            @DBName       NVARCHAR(100) = '',
            @Username     NVARCHAR(100) = '';

    SELECT @SchtaskID = SchTaskID
    FROM   ScheduledTasks
    WHERE  SchTaskName = 'CT.TL.SYS.DBArchive';

    -- Validation: Task row exists?
    IF ISNULL(@SchtaskID, 0) = 0
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] ScheduledTask [CT.TL.SYS.DBArchive] not found in ScheduledTasks table.';
        RETURN;
    END

    SELECT
        @DBServerName = MAX(CASE WHEN FieldName = 'DBServerName' THEN Value END),
        @DBName       = MAX(CASE WHEN FieldName = 'DBName'       THEN Value END),
        @Username     = MAX(CASE WHEN FieldName = 'Username'     THEN Value END)
    FROM ScheduledTaskSettings
    WHERE SchTaskID = @SchtaskID;

    -- Validation: DBServerName missing or blank?
    IF @DBServerName IS NULL OR LTRIM(RTRIM(@DBServerName)) = ''
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] [DBServerName] is missing or blank in ScheduledTaskSettings. SchTaskID = '
                               + CAST(@SchtaskID AS NVARCHAR(10));
        RETURN;
    END

    -- Validation: DBName missing or blank?
    IF @DBName IS NULL OR LTRIM(RTRIM(@DBName)) = ''
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] [DBName] is missing or blank in ScheduledTaskSettings. SchTaskID = '
                               + CAST(@SchtaskID AS NVARCHAR(10));
        RETURN;
    END

    -- Validation: Username missing or blank?
    IF @Username IS NULL OR LTRIM(RTRIM(@Username)) = ''
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] [Username] is missing or blank in ScheduledTaskSettings. SchTaskID = '
                               + CAST(@SchtaskID AS NVARCHAR(10));
        RETURN;
    END

    -- =========================================================================
    -- SECTION 3 : Build Variables
    -- =========================================================================

    -- Validation: Injection guard on DBServerName
    IF CHARINDEX(';', @DBServerName) > 0 OR CHARINDEX('''', @DBServerName) > 0
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] DBServerName contains invalid character ( ; or '' ). Possible injection risk.';
        RETURN;
    END

    DECLARE @LinkedServer    NVARCHAR(200) = 'LinkedServer';
    DECLARE @ProvStr         NVARCHAR(300) = 'Server=' + @DBServerName + ';TrustServerCertificate=Yes;';

    DECLARE @LocalServerName NVARCHAR(256) = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256));

    -- Validation: SERVERPROPERTY resolved?
    IF @LocalServerName IS NULL OR LTRIM(RTRIM(@LocalServerName)) = ''
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] SERVERPROPERTY(ServerName) returned NULL. Check server configuration.';
        RETURN;
    END

    DECLARE @LocalProvStr    NVARCHAR(300) = 'Server=' + @LocalServerName + ';TrustServerCertificate=Yes;';

    -- Validation: Remote and Local same server? (loopback conflict)
    IF @IsCreateLocalLS = 1 AND UPPER(LTRIM(RTRIM(@LocalServerName))) = UPPER(LTRIM(RTRIM(@DBServerName)))
    BEGIN
        SET @OutReturnCode   = 1;
        SET @OutErrorMessage = '[usp_LinkedServiceManager] Remote DBServerName and Local ServerName are the same ['
                               + @LocalServerName + ']. Loopback conflict — set @IsCreateLocalLS=0.';
        RETURN;
    END

    -- =========================================================================
    -- SECTION 4 : DROP Action
    -- =========================================================================
    IF @Action = 2
    BEGIN
        BEGIN TRY
            IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @LinkedServer)
            BEGIN
                EXEC sp_droplinkedsrvlogin @LinkedServer, NULL;
                EXEC sp_dropserver         @LinkedServer;
            END

            IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @LocalServerName)
            BEGIN
                EXEC sp_droplinkedsrvlogin @LocalServerName, NULL;
                EXEC sp_dropserver         @LocalServerName;
            END
        END TRY
        BEGIN CATCH
            SET @OutReturnCode   = 2;
            SET @OutErrorMessage = '[usp_LinkedServiceManager] DROP failed. Error: '
                                   + ERROR_MESSAGE()
                                   + ' | Severity: ' + CAST(ERROR_SEVERITY()  AS NVARCHAR(5))
                                   + ' | Line: '     + CAST(ERROR_LINE()      AS NVARCHAR(5));
        END CATCH

        RETURN;
    END

    -- =========================================================================
    -- SECTION 5 : CREATE Action - Remote Linked Server
    -- =========================================================================
    IF @Action = 1
    BEGIN
        BEGIN TRY
            IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @LinkedServer)
            BEGIN
                EXEC sp_droplinkedsrvlogin @LinkedServer, NULL;
                EXEC sp_dropserver         @LinkedServer;
            END

            EXEC sp_addlinkedserver
                @server     = @LinkedServer,
                @srvproduct = '',
                @provider   = 'MSOLEDBSQL',
                @provstr    = @ProvStr;

            EXEC sp_addlinkedsrvlogin
                @rmtsrvname  = @LinkedServer,
                @useself     = 'FALSE',
                @rmtuser     = @Username,
                @rmtpassword = @Password;

            EXEC sp_serveroption @LinkedServer, 'rpc',     'true';
            EXEC sp_serveroption @LinkedServer, 'rpc out', 'true';

            SET @OutLinkedServer   = @LinkedServer;
            SET @OutLinkedServerDB = '[' + @LinkedServer + '].[' + @DBName + ']';

        END TRY
        BEGIN CATCH
            -- Cleanup partial remote LS on failure
            IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @LinkedServer)
            BEGIN
                EXEC sp_droplinkedsrvlogin @LinkedServer, NULL;
                EXEC sp_dropserver         @LinkedServer;
            END

            SET @OutReturnCode   = 2;
            SET @OutErrorMessage = '[usp_LinkedServiceManager] Remote LS CREATE failed. Error: '
                                   + ERROR_MESSAGE()
                                   + ' | Severity: ' + CAST(ERROR_SEVERITY()  AS NVARCHAR(5))
                                   + ' | Line: '     + CAST(ERROR_LINE()      AS NVARCHAR(5));
            RETURN;
        END CATCH

        -- ======================================================================
        -- SECTION 6 : CREATE Action - Local Linked Server (optional)
        -- ======================================================================
        IF @IsCreateLocalLS = 1
        BEGIN
            BEGIN TRY
                IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @LocalServerName)
                BEGIN
                    EXEC sp_droplinkedsrvlogin @LocalServerName, NULL;
                    EXEC sp_dropserver         @LocalServerName;
                END

                EXEC sp_addlinkedserver
                    @server     = @LocalServerName,
                    @srvproduct = '',
                    @provider   = 'MSOLEDBSQL',
                    @provstr    = @LocalProvStr;

                EXEC sp_addlinkedsrvlogin
                    @rmtsrvname  = @LocalServerName,
                    @useself     = 'FALSE',
                    @rmtuser     = 'TLPro',
                    @rmtpassword = @LocalPassword;

                EXEC sp_serveroption @LocalServerName, 'rpc',     'true';
                EXEC sp_serveroption @LocalServerName, 'rpc out', 'true';

                SET @OutLocalLS   = @LocalServerName;
                SET @OutLocalLSDB = '[' + @LocalServerName + '].[' + DB_NAME() + ']';

            END TRY
            BEGIN CATCH
                -- Cleanup partial local LS on failure
                IF EXISTS (SELECT 1 FROM sys.servers WHERE name = @LocalServerName)
                BEGIN
                    EXEC sp_droplinkedsrvlogin @LocalServerName, NULL;
                    EXEC sp_dropserver         @LocalServerName;
                END

                SET @OutReturnCode   = 2;
                SET @OutErrorMessage = '[usp_LinkedServiceManager] Local LS CREATE failed. Error: '
                                       + ERROR_MESSAGE()
                                       + ' | Severity: ' + CAST(ERROR_SEVERITY()  AS NVARCHAR(5))
                                       + ' | Line: '     + CAST(ERROR_LINE()      AS NVARCHAR(5));
                RETURN;
            END CATCH
        END -- IF @IsCreateLocalLS = 1

        RETURN;
    END -- IF @Action = 1

END
GO

Key Features Explained

1. Input Validation

The procedure first checks:

Action is valid or not
Password is provided or not
Required config exists or not

👉 Why important?
Prevents runtime errors and wrong execution.

2. Dynamic Configuration

It reads data from:

ScheduledTasks
ScheduledTaskSettings

This means:
You don’t hardcode server details.

👉 Benefit:
Flexible and reusable system

3. Security Check (Injection Protection)

It checks invalid characters like:

;  or  '

👉 Why?
To prevent SQL Injection attacks.

4. Create Linked Server

It uses:

sp_addlinkedserver
sp_addlinkedsrvlogin

Also enables:

rpc = true
rpc out = true

👉 Meaning:
Allows remote procedure calls between servers.

5. Drop Linked Server

Before creating, it removes existing server:

sp_dropserver
sp_droplinkedsrvlogin

👉 Benefit:
Avoids duplicate or conflict issues.

6. Local Linked Server (Optional)

If:

@IsCreateLocalLS = 1

Then it also creates a loopback linked server (same server).

👉 Use Case:

Testing
Internal processing

7. Error Handling

Uses:

TRY...CATCH

Returns:

Error message
Error severity
Line number

👉 This is very important for debugging.

8. Output Parameters

The procedure returns:

Linked server name
Database path
Error code
Error message

👉 Helps in:

Logging
Debugging
Integration with other SPs

Example Usage

Create Linked Server

DECLARE @LS              NVARCHAR(200) = NULL;
DECLARE @LSDB            NVARCHAR(400) = NULL;
DECLARE @LSReturnCode    INT           = 0;
DECLARE @LSErrorMessage  NVARCHAR(2000)= '';


    EXEC usp_LinkedServiceManager
         @Action            = 1,
         @Password          = 'Fsdf#123',
         @IsCreateLocalLS   = 0,
         @LocalPassword     = '',
         @OutLinkedServer   = @LS              OUTPUT,
         @OutLinkedServerDB = @LSDB            OUTPUT,
         @OutReturnCode     = @LSReturnCode    OUTPUT,
         @OutErrorMessage   = @LSErrorMessage  OUTPUT;

    PRINT 'Linked Server      : ' + ISNULL(@LS,   'NULL');
    PRINT 'Linked Server DB   : ' + ISNULL(@LSDB, 'NULL');

    IF @LSReturnCode <> 0
    BEGIN
        PRINT 'Linked Server creation failed: ' + @LSErrorMessage;
        PRINT 'Falling back to MAIN DB (MODE = 1)';
    END

Drop Linked Server

EXEC usp_LinkedServiceManager
    @Action = 2;

Real-Life Use Case

Imagine:

You have archive database on another server
You need to fetch data daily

Instead of manual setup:
👉 This procedure automatically creates connection, runs job, and removes it

Conclusion

This stored procedure is a powerful way to:

Automate linked server management
Reduce manual work
Improve reliability
Handle errors properly

If you are working in data engineering or SQL Server automation, this approach is very useful.