SQL Server – Store Linked Server in Stored Procedure Variable

linked-serversql serversql-server-2016

I have following setup:

Server 1: CGENMONOSAMP01

Server 2: CGENMONOSAMP02

I have Server 2 linked to Server 1 and link works, I can do queries like this on Server 1:

SELECT * FROM [CGENMONOSAMP02].master.dbo.sysdatabases

And it retrieves list of DB's on Server 2.

I am trying to create the following:

    DECLARE @SourceDatabase varchar(50) = '[CGENMONOSAMP02].Sample_DB'
    DECLARE @SourceHost varchar(50) = (SELECT SUBSTRING(@SourceDatabase, 2, 14))
    DECLARE @SourceDB varchar(50) = (SELECT SUBSTRING(@SourceDatabase, 18, 100))

--@SourceHost is CGENMONOSAMP02

--@SourceDB is Sample_DB

    IF NOT EXISTS (SELECT [name] FROM master.dbo.sysdatabases WHERE [name] = @SourceDatabase)
    BEGIN
        IF NOT EXISTS (SELECT [name] FROM @SourceHost.master.dbo.sysdatabases WHERE [name] = @SourceDB) 
        BEGIN
        RAISERROR ('Error: Source Database[%s] is not existing.', 10, 1,@SourceDatabase)
        RETURN
        END
    END

This is being called on Server 1, it's supposed to take DB name which can either be name of DB located

on Server 1 as: "Sample_Table"

or it can be name of DB located

on Server 2 as: "[CGENMONOSAMP02].Sample_Table"

It's supposed to first check if passed name of DB exists on local server, if not – it should check if it exists on linked server.

I've tried the above and it gives me:

Incorrect syntax near '.'.

I've also tried:

IF NOT EXISTS (SELECT [name] FROM [@SourceHost].master.dbo.sysdatabases WHERE [name] = @SourceDB)

But that gives me:

Could not find server '@SourceHost' in sys.servers. Verify that the
correct server name was specified.

Is it possible to make such reference?

Best Answer

You could use dynamic sql to check the existence of databases in linked servers when using parameters.

An example:

DECLARE @SourceHost nvarchar(50) = '[CGENMONOSAMP02]';
DECLARE @SourceDB nvarchar(50) = 'Sample_DB';
--@SourceHost is CGENMONOSAMP02

--@SourceDB is Sample_DB
CREATE TABLE #temp ([name] nvarchar(50));
IF NOT EXISTS (SELECT [name] FROM master.sys.databases WHERE [name] = @SourceDB)
BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = 'SELECT [name] FROM '+@SourceHost+'.[master].[sys].[databases] WHERE [name] = @SourceDB ';

    INSERT INTO #temp([name])
    EXEC SP_EXECUTESQL @SQL,N'@SourceDB nvarchar(50)',@SourceDB;

    IF NOT EXISTS(SELECT [name] FROM #temp)
    BEGIN
        RAISERROR ('Error: Source Database[%s] is not existing.', 10, 1,@SourceDB);
    END
END

DROP TABLE #temp;
Related Question