How to get Physical node name at startup

• Sirish

  SSC Veteran

  Points: 242

  More actions

  December 6, 2005 at 12:03 pm

  #91032

  Hi all,

  We were trying to write a stored proc to mail whenever a server gets restarted. The problem we are facing right now is with getting the physical node name. We have used @@ServerName to get the virtual node name, and HOST_NAME() to get the physical node name. But, HOST_NAME() returns nothing for some reason (???!!!???). The same script works fine from QA. The stored Proc is configured to run at start up. It is a clustered environment.

  We have even tried to use the following code but were unsuccessful here too.

  INSERT INTO ##ClusterNode

  EXEC XP_CMDSHELL 'cluster.exe /CLUSTER:ClusterName GROUP'

  Thanks in advance..

  Sirish

• Steve Angus

  SSC Rookie

  Points: 47

  More actions

  December 7, 2005 at 4:52 am

  #608140

  Sirish,

  Something else you could try is

  INSERT INTO ##ClusterNode

  EXEC XP_CMDSHELL 'SET COMPUTERNAME'

  ... then substring the result if you get anything sensible back.

• Jeff Gray

  SSChampion

  Points: 10667

  More actions

  December 7, 2005 at 12:11 pm

  #608244

  SET COMPUTERNAME returns the virtual server's NBNAME, which is the same no matter what physical node it's running on.

   

  Try this:

  create table #foo (

  id tinyint identity(1,1),

  txt varchar(255))

  Insert #foo (txt)

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

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

  drop table #foo

   

  This should return the FQDN of the physical server on which your virtual server is running.

  jg