动态SQL中使用OPENDATASOURCE连接远程SQL Server报错如何解决

问题原因 #

你的代码存在两个核心错误：

• 服务器名拼接格式错误：你存储在@myTableVariable里的服务器名带有方括号（如[TESLABSQL01T]），但OPENDATASOURCE的连接字符串中Server参数不需要包裹方括号，拼接后生成的连接串为Server=[TESLABSQL01T]，系统无法识别带方括号的服务器地址，直接触发连接超时报错。你单独测试时直接写了不带方括号的服务器名，因此可以正常运行。
• 关联表语法错误：动态SQL中LEFT JOIN .master.sys.sysprocesses开头多了冗余的圆点，且如果需要和最初的链接服务器版本逻辑一致（关联远程服务器的进程表），此处也需要补充OPENDATASOURCE前缀。

修正后代码 #

DECLARE @ServerName varchar(50), @DynamicSQL NVARCHAR(MAX)
-- 注意此处存储的服务器名去掉了方括号
DECLARE @myTableVariable TABLE (id INT, ServerName varchar(50))
insert into @myTableVariable values(1,'TESLABSQL01T'),(2,'TESLABSQL02T')
select * from @myTableVariable

Declare VarCursor cursor for
Select ServerName from @myTableVariable
Open VarCursor
FETCH NEXT FROM VarCursor INTO @ServerName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @DynamicSQL='SELECT @@ServerName AS [ServerName]
                 ,NAME AS DatabaseName 
                 ,COUNT(STATUS) AS [NumberOfConnections]
                 ,GETDATE() AS [TimeStamp]
                 ,hostname
                 ,program_name
                 ,loginame
    FROM OPENDATASOURCE(''SQLNCLI'', ''Server='+@ServerName+';TrustServerCertificate=Yes;Trusted_Connection=Yes;'').master.sys.databases sd
    LEFT JOIN OPENDATASOURCE(''SQLNCLI'', ''Server='+@ServerName+';TrustServerCertificate=Yes;Trusted_Connection=Yes;'').master.sys.sysprocesses sp ON sd.database_id = sp.dbid
    WHERE database_id NOT BETWEEN 1 AND 4
    GROUP BY NAME,hostname ,program_name ,loginame'
    
    EXEC (@DynamicSQL)

    FETCH NEXT FROM VarCursor INTO @ServerName
END
CLOSE VarCursor
DEALLOCATE VarCursor

如果你确实需要关联本地实例的sysprocesses表，只需要删掉LEFT JOIN前多余的圆点即可：

LEFT JOIN master.sys.sysprocesses sp ON sd.database_id = sp.dbid

内容的提问来源于stack exchange，提问作者Francesco Mantovani