sql-server - 为什么 SQL Server @@SERVERNAME 返回我的旧计算机名称？

Question

我刚刚在 SQL Server 2008 R2 中偶然发现了一个问题 - 当我调用 @@SERVERNAME 时，它返回的是我的旧计算机的计算机名称，而不是当前计算机的名称。为什么是这样？我该如何解决它？ SQL Server 不知何故记住了旧的机器名称。

Answer 1

这是众所周知的并有记录，请参阅Rename a Computer that Hosts a Stand-Alone Instance of SQL Server :

When you change the name of the computer that is running SQL Server, the new name is recognized during SQL Server startup. You do not have to run Setup again to reset the computer name. Instead, use the following steps to update system metadata that is stored in sys.servers and reported by the system function @@SERVERNAME:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

您还可以使用SERVERPROPERTY('MachineName')保证始终返回更新的名称:

MachineName Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.

SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 将返回群集中的当前事件节点，或与非群集实例上的“MachineName”相同的值。

编辑(由第 3 方)添加 WEFX 的评论，以防有人错过:

Also, you'll need to restart your SQL services (or reboot the SQL Server) in order for SELECT @@SERVERNAME to return the accurate (new) servername