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