Get Active Cluster Node

-- 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