dynamic sql - Possible ways of updating the catalog of an already created Linked server - Stack Overflow

admin管理员组

文章数量:1401820

Reason for change

I have a main Staging server with multiple linked server's setup. Each one of these Linked Servers have their own DB on the main Staging Server. I have an SP that extracts data from these linked servers and imports it into my main Staging server per DB per linked server. My issue now is that some of these linked servers have more than 1 valid DB that gets used. Previously I assumed this DB was always called "OriginalDB" I have now found out this is not the case causing the below line to not always be valid. I should also note this is a line within a block of dynamic sql

SELECT [Store ID] FROM [' + @LinkedServerName + '].OriginalDB.dbo.[Branch Details] WHERE [Branch Number] = 1) AS StoreCode

So i need to change OriginalDB dynamically to the correct DB name as well, somewhat like what i did with LinkedServer. Im not including the main SP which the above code came from as the issue does lie within this. (Unless there is no fix with my current solution) This script will also only be ran once, but there are A LOT of Db it needs to be done on and i dont want to do it manually (and this will make it easier for the future)

Main Issue

But in order to do this i need to update the Catalog column in sys.servers to the correct DB name for each linked server. I've created a variable in my SP called @catalog which i can then just select the catalog name for each linked server.

This is my current script trying to update the catalog using sp_serveroption

DECLARE @DBName NVARCHAR(255);
DECLARE @LinkedServerName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Count INT = 0; -- Counter for the number of matching databases
DECLARE @Result NVARCHAR(255); -- Variable to store the result for DB name or "Multiple DBs"

-- Set Linked Server name based on current DB
SET @LinkedServerName = RIGHT(DB_NAME(), LEN(DB_NAME()) - PATINDEX('%[0-9]%', DB_NAME()) + 1);

SET @Result = '';

SET @SQL = '
DECLARE db_cursor CURSOR FOR
SELECT name
FROM [' + @LinkedServerName + '].master.sys.databases 
WHERE state_desc = ''ONLINE'' 
  AND name != ''NotOriginalDB'';'

EXEC sp_executesql @SQL;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Define the dynamic SQL for checking tables
    SET @SQL = '
    IF EXISTS (SELECT 1 FROM [' + @LinkedServerName + '].[' + @DBName + '].sys.tables 
                WHERE name IN (''Table One'', ''Table Two'')) 
    BEGIN
        -- Increment the counter if a match is found
        SET @Count = @Count + 1;
        -- If more than 1 match, set the result to "Multiple DBs" and exit early
        IF @Count > 1
        BEGIN
            -- Output "Multiple DBs"
            SET @Result = ''Multiple DBs'';
            CLOSE db_cursor;
            DEALLOCATE db_cursor;
            RETURN;
        END;
    END';

    -- Execute the dynamic SQL for checking tables
    EXEC sp_executesql @SQL, N'@Count INT OUTPUT, @Result NVARCHAR(255) OUTPUT', @Count OUTPUT, @Result OUTPUT;

    FETCH NEXT FROM db_cursor INTO @DBName;
END;

-- If only 1 match, set the result to the database name
IF @Count = 1
BEGIN
    SET @Result = @DBName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

IF @Result != ''
BEGIN
    EXEC sp_serveroption @server = @LinkedServerName, @optname = 'catalog', @optvalue = @Result;
END;

-- Select the result for verification
SELECT @Result AS DatabaseResult;

I also tried to just use a normal update script but it gives me a "Ad hoc updates to system catalogs are not allowed" error. I also tried to bypass this by using

EXEC sp_configure 'allow updates', 1;
RECONFIGURE;

but i get another error "Ad hoc update to system catalogs is not supported." Which apparently is because this functionality has been disabled by Microsoft due to possibility of corruption, but IDK.

Now back to my script which uses sp_serveroption, this produces an error of

Msg 15600, Level 15, State 1, Procedure sp_serveroption, Line 203 [Batch Start Line 0]
An invalid parameter or option was specified for procedure 'sys.sp_serveroption'.

Which, luckily for me is also caused by the catalog not being changeable via this SP... This script does still produce the correct result in the results tab.

Some Extra Info on how the process works

The script is always ran on the correct DB by making use of a bat file (also note the script depends on being run on the correct DB), this same bat file also gets used to execute the script which gets saved as a .sql file. The naming convention of the Linked server is: The name = digits at the end of the current DB name (AKA LinkedServerName)

Reason for change

I have a main Staging server with multiple linked server's setup. Each one of these Linked Servers have their own DB on the main Staging Server. I have an SP that extracts data from these linked servers and imports it into my main Staging server per DB per linked server. My issue now is that some of these linked servers have more than 1 valid DB that gets used. Previously I assumed this DB was always called "OriginalDB" I have now found out this is not the case causing the below line to not always be valid. I should also note this is a line within a block of dynamic sql

SELECT [Store ID] FROM [' + @LinkedServerName + '].OriginalDB.dbo.[Branch Details] WHERE [Branch Number] = 1) AS StoreCode

So i need to change OriginalDB dynamically to the correct DB name as well, somewhat like what i did with LinkedServer. Im not including the main SP which the above code came from as the issue does lie within this. (Unless there is no fix with my current solution) This script will also only be ran once, but there are A LOT of Db it needs to be done on and i dont want to do it manually (and this will make it easier for the future)

Main Issue

But in order to do this i need to update the Catalog column in sys.servers to the correct DB name for each linked server. I've created a variable in my SP called @catalog which i can then just select the catalog name for each linked server.

This is my current script trying to update the catalog using sp_serveroption

DECLARE @DBName NVARCHAR(255);
DECLARE @LinkedServerName NVARCHAR(255);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @Count INT = 0; -- Counter for the number of matching databases
DECLARE @Result NVARCHAR(255); -- Variable to store the result for DB name or "Multiple DBs"

-- Set Linked Server name based on current DB
SET @LinkedServerName = RIGHT(DB_NAME(), LEN(DB_NAME()) - PATINDEX('%[0-9]%', DB_NAME()) + 1);

SET @Result = '';

SET @SQL = '
DECLARE db_cursor CURSOR FOR
SELECT name
FROM [' + @LinkedServerName + '].master.sys.databases 
WHERE state_desc = ''ONLINE'' 
  AND name != ''NotOriginalDB'';'

EXEC sp_executesql @SQL;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Define the dynamic SQL for checking tables
    SET @SQL = '
    IF EXISTS (SELECT 1 FROM [' + @LinkedServerName + '].[' + @DBName + '].sys.tables 
                WHERE name IN (''Table One'', ''Table Two'')) 
    BEGIN
        -- Increment the counter if a match is found
        SET @Count = @Count + 1;
        -- If more than 1 match, set the result to "Multiple DBs" and exit early
        IF @Count > 1
        BEGIN
            -- Output "Multiple DBs"
            SET @Result = ''Multiple DBs'';
            CLOSE db_cursor;
            DEALLOCATE db_cursor;
            RETURN;
        END;
    END';

    -- Execute the dynamic SQL for checking tables
    EXEC sp_executesql @SQL, N'@Count INT OUTPUT, @Result NVARCHAR(255) OUTPUT', @Count OUTPUT, @Result OUTPUT;

    FETCH NEXT FROM db_cursor INTO @DBName;
END;

-- If only 1 match, set the result to the database name
IF @Count = 1
BEGIN
    SET @Result = @DBName;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

IF @Result != ''
BEGIN
    EXEC sp_serveroption @server = @LinkedServerName, @optname = 'catalog', @optvalue = @Result;
END;

-- Select the result for verification
SELECT @Result AS DatabaseResult;

I also tried to just use a normal update script but it gives me a "Ad hoc updates to system catalogs are not allowed" error. I also tried to bypass this by using

EXEC sp_configure 'allow updates', 1;
RECONFIGURE;

but i get another error "Ad hoc update to system catalogs is not supported." Which apparently is because this functionality has been disabled by Microsoft due to possibility of corruption, but IDK.

Now back to my script which uses sp_serveroption, this produces an error of

Msg 15600, Level 15, State 1, Procedure sp_serveroption, Line 203 [Batch Start Line 0]
An invalid parameter or option was specified for procedure 'sys.sp_serveroption'.

Which, luckily for me is also caused by the catalog not being changeable via this SP... This script does still produce the correct result in the results tab.

Some Extra Info on how the process works

The script is always ran on the correct DB by making use of a bat file (also note the script depends on being run on the correct DB), this same bat file also gets used to execute the script which gets saved as a .sql file. The naming convention of the Linked server is: The name = digits at the end of the current DB name (AKA LinkedServerName)

• sql-server
• dynamic-sql
• linked-server

Share Improve this question Follow edited Mar 24 at 19:02 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Mar 24 at 16:38 EnzoEnzo 322 bronze badges 6

• "i need to update the Catalog column in sys.servers to the correct DB name for each linked server" why do you need to do this at all? Why not just do [OriginalDB] dynamically as well, by querying LinkedServer.master.sys.databases on the fly? – Charlieface Commented Mar 24 at 16:41
• LinkedServer.master.sys.databases brings back more than just the correct "OriginalDB", which is why i created the above script to specify which are correct by way of table names. But i also have to keep into account that some linked servers have more than 1 correct "OriginalDB" which is why those get called "Multiple DBs" I hope i understood your reply correctly. – Enzo Commented Mar 24 at 16:54
• FYI, syntax like N'...[' + @Variable+ N']...' is NOT injection safe; just like single quotes in an injected "parameter", brackets can also be escaped. If you need to inject an object name you should always use QUOTENAME. Also the correct datatype for an object name is sysname (a synonym for nvarchar(128) NOT NULL). db<>fiddle – Thom A Commented Mar 24 at 16:56
• No I'm still not getting it, you should be able to select which database you want using some criteria or create a dynamic script targetting multiple databases. Perhaps you should show the full dynamic SQL script, because this whole thing looks like an XY Problem. – Charlieface Commented Mar 24 at 17:17
• You can't change the catalog name after creating the server due to... reasons, so the best is probably either re-create the servers which is likely bad idea, or not rely on catalogue property at all if that's possible – siggemannen Commented Mar 24 at 18:53

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

Changing the catalog value for an already created linked server isnt possible without dropping and re-creating the linked server. Thanks for everyone's time and effort.

本文标签： dynamic sqlPossible ways of updating the catalog of an already created Linked serverStack Overflow