[SOLVED] Possible ways of updating the catalog of an already created Linked server

Possible ways of updating the catalog of an already created Linked server

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)

Solution

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.