修改 Windows 主機名後,@@SERVERNAME (舊名) 與 SERVERPROPERTY('ServerName') (新名) 不一致,導致:
sp_addlinkedserver 驗證失敗SELECT
@@SERVERNAME AS Global_Variable,
SERVERPROPERTY('ServerName') AS Property_Function;
-- 兩者輸出不同即不一致
USE master;
GO
DECLARE @serverproperty_servername NVARCHAR(128),
@servername NVARCHAR(128);
SELECT @serverproperty_servername = CONVERT(NVARCHAR(128), SERVERPROPERTY('ServerName'));
SELECT @servername = CONVERT(NVARCHAR(128), @@SERVERNAME);
PRINT 'Current @@SERVERNAME: ' + @servername;
PRINT 'Current SERVERPROPERTY: ' + @serverproperty_servername;
IF @serverproperty_servername <> @servername
BEGIN
-- 1. 刪除錯誤的伺服器名
EXEC sp_dropserver @server = @servername;
-- 2. 添加正確的伺服器名並標記為本地
EXEC sp_addserver @server = @serverproperty_servername, @local = 'LOCAL';
PRINT 'Fixed. Please RESTART SQL Server service to take effect.';
END
ELSE
BEGIN
PRINT 'Already consistent. No action needed.';
END
-- 重啟 SQL Server 服務後
SELECT @@SERVERNAME, SERVERPROPERTY('ServerName');
-- 兩者應相同
@@SERVERNAME:安裝時寫入系統表 sys.servers (server_id=0),**不自動更新**SERVERPROPERTY('ServerName'):運行時從 OS 獲取,**實時反映主機名**sp_dropserver 移除 sys.servers 中 server_id=0 行sp_addserver @local='LOCAL' 重新插入 server_id=0 行,同步為當前主機名$sql = @"
USE master;
DECLARE @sn NVARCHAR(128) = CONVERT(NVARCHAR(128), SERVERPROPERTY('ServerName'));
DECLARE @sn2 NVARCHAR(128) = CONVERT(NVARCHAR(128), @@SERVERNAME);
IF @sn <> @sn2 BEGIN
EXEC sp_dropserver @sn2;
EXEC sp_addserver @sn, @local='LOCAL';
PRINT 'Fixed, restart required.';
END
"@
Invoke-Sqlcmd -Query $sql -ServerInstance "."
**核心:主機名變更後,必跑此腳本同步 @@SERVERNAME,否則高可用/複制功能全掛。**