MS SQL Server – Cluster – Get Active Node

The Stored Procedure (SP) pasted below is useful when one is trying to determine the active node from a MS SQL Server Cluster Install.

use DBA
go

if object_id('dbo.usp_Cluster_GetActiveNode') is not null
begin
drop proc dbo.usp_Cluster_GetActiveNode
end
go

create proc dbo.usp_Cluster_GetActiveNode
(
   @computerName varchar(255) = null output
)

set nocount on

if object_id('tempdb..#regValues') is not null
begin
   print 'Dropping table #regValues...'
   drop table #regValues
end

create table #regValues
(
     Value varchar(255)
   , Data varchar(255)
)

--Note ComputerNamePhysicalNetBIOS only works in MS SQL Server 2005 and above
select @computerName = cast(serverproperty('ComputerNamePhysicalNetBIOS') as varchar(255))

if ((@computerName is null) or (@computerName = ''))
begin

      insert into #regValues
      exec master..xp_regread
               'HKEY_LOCAL_Machine'
             , 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\'
             , 'ComputerName'

      select @computerName = [Data]
      from #regValues

end

if object_id('tempdb..#regValues') is not null
begin
      print 'Dropping table #regValues...'
      drop table #regValues
end

go

/*

-- Sample invocation code

declare @computerName varchar(255)

exec dbo.usp_Cluster_GetActiveNode
         @computerName output

print 'Computer name is ' + isNull(@computerName, '')

*/

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s