A Handy Server Property in SQL2005

Linchi, A simple T-SQL process that will work for any version of SQL (7,2k,2k5) is xp_cmdshell 'ping -a 127.0.0.1'

this will work for any SQL server and returns the fully qualified name of the host.  In the case of a clustered SQL server it returns the Host name that the virtual SQL server is currently running from.

For a clean output of just the fully qualified host name,

SET ROWCOUNT 2

CREATE TABLE #H ([output] varchar(128))

INSERT INTO #H EXEC master..xp_cmdshell 'ping -a 127.0.0.1'

SET ROWCOUNT 1

DELETE #H

SET ROWCOUNT 0

SELECT SUBSTRING([output],9,(CHARINDEX(' '[',[output])-9)) FROM #H

DROP TABLE #H

Dan

That's excellent!

I did notice that sometimes >ping -a 127.0.0.1< may not return with the computer name. For instance, you may get the string localhost. I don't know under what precise condition you'd be guaranteed to get the FQDN.

This is what I use to get the physical host:

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

I know it's an 'undocumented' procedure, but what about xp_regread.  

DECLARE @ValueOut varchar(20)

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

 @key='SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName',

 @value_name='ComputerName',

 @value=@ValueOut OUTPUT

SELECT @ValueOut

Funny, I mentioned this serverproperty in a request for one that returns last failover time.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263923

It's been my observation that the output from fn_virtualservernodes will list the active node first in the output.

quero saber como faço para instalar o handy café