Back to all posts
SQL SQL

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...

Author

SQLDataDev Editorial Team

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:

SQL 
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:

SQL 
usp_LinkedServiceManager

It supports two actions:

  • @Action = 1 → Create Linked Server

  • @Action = 2 → Drop Linked Server

SQL 
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:

SQL 
;  or  '

👉 Why?
To prevent SQL Injection attacks.

4. Create Linked Server

It uses:

SQL 
sp_addlinkedserver
sp_addlinkedsrvlogin

Also enables:

SQL 
rpc = true
rpc out = true

👉 Meaning:
Allows remote procedure calls between servers.

5. Drop Linked Server

Before creating, it removes existing server:

SQL 
sp_dropserver
sp_droplinkedsrvlogin

👉 Benefit:
Avoids duplicate or conflict issues.

6. Local Linked Server (Optional)

If:

SQL 
@IsCreateLocalLS = 1

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

👉 Use Case:

  • Testing

  • Internal processing

7. Error Handling

Uses:

SQL 
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

SQL 
DECLARE @RC INT, @Err NVARCHAR(1000);

EXEC usp_LinkedServiceManager
    @Action = 1,
    @Password = 'YourPassword',
    @OutReturnCode = @RC OUTPUT,
    @OutErrorMessage = @Err OUTPUT;

IF @RC <> 0
    PRINT @Err;

Drop Linked Server

SQL 
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.

Tags

SQL ServerLinked ServerStored ProcedureDatabase AutomationSQL Server 2012Dynamic SQLData EngineeringSQL Best PracticesError HandlingDatabase Connectivity

Keep building your data skillset

Explore more SQL, Python, analytics, and engineering tutorials.

Browse All Posts