Programmatically replacing linked server references with local database references in SQL Server stored procs?

Question

As a part of a scripted procedure I'm trying to programmatically update references to linked servers in stored procs. We have several references like this:-

SELECT foo, bar
FROM [Server].[Database].dbo.[Table]

Which I would like to translate to:-

SELECT foo, bar
FROM [Database].dbo.[Table]

I would like to do this entirely programmatically in a 'fire and forget' script across several databases.

The idea I have right now is to use metadata to find references to linked tables, read the text of each sp from metadata again, adjust each sp's text, then shove each block of updated text into an exec statement to rebuild 'em one-by-one.

I do wonder whether this will be a humongous pain however, so does anybody have any better ideas? I am open to using powershell if that could provide a better solution.

Thanks in advance!

Answer 1

Hopefully I am understanding the questions, but rather than removing or replacing [Server], I suggest one of two approaches:

• Option 1: Don't change any of the SPs. Instead, update the linked server configuration to point a different database, even the local box.

• Option 2: Don't change any of the SPs. Instead, start using SQL Server Aliases. SQL Server Aliases are managed via the CliConfig utility and are ultimately stored in the registry. Thus, they can be applied manually or via .reg script. Basically, the SQL Server Alias deciphers the server (along with port) which is being referenced. If you update the link server configuration to reference the SQL Server Alias rather than a specific server, you can point your procedures to different server (even the local server) whenever you would like.

I hope it helps.

Answer 2

Your approach is the easiest, frankly. I had a similar issue earlier this year

• Read sys.sql_modules
• REPLACE the linked server text and CREATE -> ALTER
• EXEC (@Result)

Answer 3

Here's a script to find all procs/functions/views that reference linked servers on a SQL 2005 instance - might be useful too:

USE master
GO
SET NOCOUNT ON;

--------------------------------------------------------------------
-- Test linked server connections
--------------------------------------------------------------------
BEGIN TRY   DROP TABLE #Svrs; END TRY BEGIN CATCH END CATCH;

CREATE TABLE #Svrs
(
    [Server]    nvarchar(max),
    [CanConnectAsDefault] bit
);

DECLARE @ServerName nvarchar(max), @RetVal int;

DECLARE Svrs CURSOR FAST_FORWARD READ_ONLY
FOR
    SELECT ServerName = S.name
    FROM sys.servers S;

OPEN Svrs;
FETCH NEXT FROM Svrs INTO @ServerName;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
     EXEC @RetVal = sys.sp_testlinkedserver @ServerName;
    END TRY
    BEGIN CATCH
     SET @RetVal = sign(@@error);
    END CATCH;

    INSERT INTO #Svrs 
    SELECT @ServerName 
     , CASE WHEN @RetVal = 0 THEN 1 ELSE 0 END;

    FETCH NEXT FROM Svrs INTO @ServerName;
END;
CLOSE Svrs;
DEALLOCATE Svrs;

SELECT * FROM #Svrs
DROP TABLE #Svrs;
GO

--------------------------------------------------------------------
-- Report linked server references
--------------------------------------------------------------------
BEGIN TRY   DROP TABLE #Refs; END TRY BEGIN CATCH END CATCH;

CREATE TABLE #Refs
(
    [Server] nvarchar(max),
    [Database] nvarchar(max),
    [Schema] nvarchar(max),
    [Object] nvarchar(max),
    [Type]  nvarchar(max)
);

DECLARE @DatabaseName nvarchar(max), @ServerName nvarchar(max), @SQL nvarchar(max);
DECLARE Refs CURSOR FAST_FORWARD READ_ONLY
FOR
    SELECT DatabaseName = D.name 
     , ServerName = S.name
    -- , ServerProvider = S.provider
    -- , ServerSource = S.data_source
    FROM sys.databases D 
      CROSS JOIN sys.servers S
    WHERE D.name NOT IN ('master', 'tempdb', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB');

OPEN Refs;
FETCH NEXT FROM Refs INTO @DatabaseName, @ServerName;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'USE [' + @DatabaseName + '];    
       INSERT INTO #Refs 
       SELECT DISTINCT ''' + @ServerName + ''', ''' + @DatabaseName + ''', S.[name], O.[name], O.type_desc 
       FROM syscomments C
         INNER JOIN sys.objects O ON C.id = O.[object_id]
         LEFT JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
       WHERE C.[TEXT] LIKE ''%[ ,~[( '''']' + @ServerName + '[ .,~])'''' ]%'' ESCAPE ''~'';'

    PRINT 'Looking for ' + @ServerName + ' refs in ' + @DatabaseName -- + ': ' + @SQL;

    EXEC sp_executesql @SQL;

    FETCH NEXT FROM Refs INTO @DatabaseName, @ServerName;
END
CLOSE Refs;
DEALLOCATE Refs;

SELECT * FROM #Refs
DROP TABLE #Refs;
GO

--------------------------------------------------------------------
SET NOCOUNT OFF;
GO

Answer 4

This is not going to be a good idea for a production environment, but if you need a loopback linked server for dev purposes this worked for me:

EXEC sp_addlinkedserver @server = N'name_for_linked_server',
    @srvproduct = N' ',
    @provider = N'SQLNCLI', 
    @datasrc = N'name_of_my_sqlserver_instance', 
    @catalog = N'name_of_database'