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

希望我能理解这些问题,但与其删除或更换[服务器],我建议使用以下两种方法之一:

• 选项1:不要改变 点a的服务器配置 不同的数据库,甚至是本地数据库

• 选项2:不要改变 别名。SQL Server别名是 通过CliConfig实用程序和 登记处。因此,它们可以被应用 手动或通过.reg脚本。 解密服务器(以及 端口)被引用。如果 引用SQL的配置 服务器别名而不是特定的 想要。

我希望有帮助。

• 阅读sql模块
• 替换链接服务器文本并创建->更改

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

对于生产环境来说,这不是一个好主意,但是如果您需要一个用于开发的环回链接服务器的话 this 为我工作:

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'