以编程方式将链接服务器引用替换为 SQL Server 存储过程中的本地数据库引用？

Question

作为脚本化过程的一部分，我试图以编程方式更新存储过程中对链接服务器的引用。 我们有几个这样的参考文献：-

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

我想将其翻译为：-

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

我想在跨多个数据库的“即发即忘”脚本中完全以编程方式执行此操作。

我现在的想法是使用元数据查找对链接表的引用，再次从元数据中读取每个 sp 的文本，调整每个 sp 的文本，然后将每个更新文本块推入 exec 语句中以逐个重建它们-一。

我确实想知道这是否会是一个巨大的痛苦，那么有人有更好的想法吗？ 如果可以提供更好的解决方案，我愿意使用 powershell。

提前致谢！

Answer 1

坦率地说，你的方法是最简单的。 今年早些时候我遇到了类似的问题

• 阅读 sys.sql_modules
• REPLACE the linked server text and CREATE -> 更改
• 执行（@Result）

Answer 2

下面是一个脚本，用于查找引用 SQL 2005 实例上的链接服务器的所有过程/函数/视图 - 可能也很有用：

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 3

对于生产环境来说这不是一个好主意，但如果您需要一个用于开发目的的环回链接服务器这对我有用：

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' 

Answer 4

希望我能理解这些问题，但我建议采用以下两种方法之一：

• 选项 1：不要更改任何服务器， 而不是删除或替换 [服务器]
  SP。 相反，更新链接
  服务器配置指向一个
  不同的数据库，甚至是本地的
  框。

• 选项 2：不要更改任何
  SP。 相反，开始使用 SQL Server
  别名。 SQL Server 别名是
  通过 CliConfig 实用程序管理和
  最终存储在
  注册表。 因此，它们可以应用
  手动或通过 .reg 脚本。
  基本上，SQL Server 别名
  破译服务器（以及
  端口）正在被引用。 如果
  您更新链接服务器
  引用 SQL 的配置
  服务器别名而不是特定的
  服务器，你可以点你的程序
  每当您时到不同的服务器（甚至本地服务器）
  想。

我希望它有帮助。