host_name() on cluster returns different value in DB Mail

Question

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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: 135369 More actions · Answer 5

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 😀