how to query the SQL server name?

I use :

select @.@.servername to find SQL server name. But it will return the virtual OS name in cluster environment, not SQL server name. I need this server name for error handling as well as generating query scripts.

How to query SQL server name?

You can:

-Query the name through xp_cmdshell, reading it from the SET command
-Creating your own CLR function to read environment variables
-Read the registry for the needed value through xp_regread /buit be careful this is undocumented and might be deprecated in further versions)

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

If you are using SQL Server 2005, then you can use the SERVERPROPERTY built-in to get the various names.

select SERVERPROPERTY('MachineName') as [Cluster Virtual Server Name]

, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [Cluster Node Name]

Otherwise, you will have to get the netbios name using any of the OS utilities.

|||

1. Suppose I have serveral instance on a cluster, how do I figure out my current connecting virtual sql server name by querying in CMD, or regitry? I do not see how.

2. Wrote CLR will incur higher cost (time to create and install, and maitenance). The cost out weights benefit. I donot want to do.

3. xp_cmdshell is disabled due to our security concern on production system. We do not have plan to use it.

|||I did the query but the SERVERPROPERTY('MachineName') returns OS virtual name not SQL virtual name. I had looked into other serverproperty parameter, none of them will return current SQL virtual server name.