THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

A Handy Server Property in SQL2005

One frequenctly asked question is, "How do I find out via T-SQL which cluster node is running this SQL Server instance?"

There is no easy way in SQL Server 2000 or SQL Server 7.0 to determine the current node of a SQL Server instance using T-SQL. The approach I use often is to run cluster.exe through xp_cmdshell, assuming you know the SQL Server resource name or the group name of your current SQL instance. You can then read the output to determine the current node. But it's rather ugly if you want to use the information programmatically in a T-SQL script.

By the way, serverproperty('MachineName') gives you the virtual server name of the current instance, serverproperty('ServerName') gives you <virtual server name>\<instance name>, and serverproperty('InstanceName') gives you the current instance name without the virtual server name prefix.

EXEC xp_cmshell 'hostname' gives you the current virtual server name, so does EXEC xp_cmdshell 'echo %computername%'.

Good news is that in SQL Server 2005 there is a server property called ComputerNamePhysicalNetBIOS that will return the current cluster node name, a T-SQL property that is long overdue.

Published Thursday, March 22, 2007 2:08 PM by Linchi Shea
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

DanMcClain said:

Linchi, A simple T-SQL process that will work for any version of SQL (7,2k,2k5) is xp_cmdshell 'ping -a 127.0.0.1'

this will work for any SQL server and returns the fully qualified name of the host.  In the case of a clustered SQL server it returns the Host name that the virtual SQL server is currently running from.

For a clean output of just the fully qualified host name,

SET ROWCOUNT 2

CREATE TABLE #H ([output] varchar(128))

INSERT INTO #H EXEC master..xp_cmdshell 'ping -a 127.0.0.1'

SET ROWCOUNT 1

DELETE #H

SET ROWCOUNT 0

SELECT SUBSTRING([output],9,(CHARINDEX(' '[',[output])-9)) FROM #H

DROP TABLE #H

Dan

March 25, 2007 12:17 PM
 

Linchi Shea said:

That's excellent!

I did notice that sometimes >ping -a 127.0.0.1< may not return with the computer name. For instance, you may get the string localhost. I don't know under what precise condition you'd be guaranteed to get the FQDN.

March 25, 2007 4:59 PM
 

andrew said:

This is what I use to get the physical host:

create table #temp (

id tinyint identity(1,1),

txt varchar(255))

Insert #temp (txt)

exec master.dbo.xp_cmdshell 'ping localhost -n 1'

select substring(txt,9,charindex('[',txt)-10) from #temp where id=2

drop table #temp

March 26, 2007 9:18 AM
 

Josh Morrison said:

I know it's an 'undocumented' procedure, but what about xp_regread.  

DECLARE @ValueOut varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

 @key='SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName',

 @value_name='ComputerName',

 @value=@ValueOut OUTPUT

SELECT @ValueOut

March 26, 2007 12:58 PM
 

AaronBertrand said:

Funny, I mentioned this serverproperty in a request for one that returns last failover time.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263923

March 27, 2007 10:47 AM
 

Gordon R. said:

It's been my observation that the output from fn_virtualservernodes will list the active node first in the output.

March 27, 2007 2:49 PM
 

angelo said:

quero saber como faço para instalar o handy café

July 5, 2007 8:04 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement