Get Active Cluster Node

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

Credits:

Author: ChillyDBA

Date: 25 May 2012

Description

Two slightly different methods. SQL 2000 has a differnt property.

Code

For SQL 2000:

-- for SQL 2000

-- this will return just the instance name, so is not really good enough

SELECT SERVERPROPERTY('InstanceName')

-- the following code will return the machine name, but does rely on xp_cmdshell

-- which can be a problem as most systemsleave it disabled

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

For Other versions:


SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')