host_name() on cluster returns different value in DB Mail

• 2 Tim 3:16

  SSCarpal Tunnel

  Points: 4541

  More actions

  March 20, 2009 at 8:34 am

  #129966

  Cluster Environment, SQL 2005, SP3:

  select host_name() returns the actual physical host name. (Just what I need).

  However, when I try to put this in an sp_send_dbmail, it returns the network name, not the actual physical box name. (NOT what I need).

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'DBMail',

  @recipients = 'name@company.com',

  @query = 'select ''Server Running on - '', host_name()' ,

  @subject = 'Server Fail-Over Alert',

  @attach_query_result_as_file = 0 ;

  When a cluster fail-over occurs, I want an email with a message telling me what side it's currently running on. (above text in a job that runs at instance start-up)

  Why does dbmail use the network name and not the actual physical host name? How can I get dbmail to us the physical box name?

  Thanks very very

  Tim White

• dmc-608719

  SSCarpal Tunnel

  Points: 4663

  More actions

  March 20, 2009 at 9:48 am

  #963626

  I think most everything built stock will return the virtual name. @@ServerName, ServerProperty(), etc will all return the virtual name. I think you may have to resort to a command shell call, a .net function, or even possibly a wmi query to get it. Hopefully someone knows a stock function, but I am not aware of one.

• dmc-608719

  SSCarpal Tunnel

  Points: 4663

  More actions

  March 20, 2009 at 10:22 am

  #963656

  Decided to do a quick search and found an arcticle referencing this code. Seems to work on my 2K5 cluster.

  SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ActiveNode

• Richard Fryar

  Hall of Fame

  Points: 3457

  More actions

  March 20, 2009 at 11:20 am

  #963704

  HOST_NAME() returns the connected host not the physical clustered server.

  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') will do what you want.

  
  Check Your SQL Servers Quickly and Easily
  www.sqlcopilot.com

• 2 Tim 3:16

  SSCarpal Tunnel

  Points: 4541

  More actions

  March 20, 2009 at 1:04 pm

  #963736

  Thanks very, just what I was looking for.

  The email I receive has a lot of blank lines, followed by a lot of dashed-lines, then I get the host name I'm looking for.

  How can I remove all the junk up front on the email?

  Thanks

  Tim White

• Johan Bijnens

  SSC Guru

  Points: 135536

  More actions

  March 20, 2009 at 1:22 pm

  #963746

  Did you try to do it like this ?

  Declare @ComputerNamePhysicalNetBIOS varchar(256)

  Set @ComputerNamePhysicalNetBIOS = 'Server [' + @@servername + '] Running on - ' + Convert(varchar(128),SERVERPROPERTY('ComputerNamePhysicalNetBIOS') )

  Declare @Subject varchar(256)

  Select @Subject = 'Server Fail-Over Alert [' + + @@servername + ']'

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'DBMail',

  @recipients = 'name@company.com',

  @Body = @ComputerNamePhysicalNetBIOS ,

  @subject = @Subject ;

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• 2 Tim 3:16

  SSCarpal Tunnel

  Points: 4541

  More actions

  March 20, 2009 at 1:27 pm

  #963750

  Perfect ! Thanks so much for all the help.

  Tim White