Obtaining the name of machine you are running a SQL statement from

Question

Post reply

Obtaining the name of machine you are running a SQL statement from

thegreatelectron

SSC Rookie

Points: 33
More actions
June 29, 2009 at 3:47 am

#207686

Is there any way in SQL to obtain the name of the machine you are connecting from to issue a command.
In other words - say I'm on the MACHINE1, logged into management studio but connected to MACHINE2. I can do SELECT @@SERVERNAME, but that will return MACHINE2; I want a function that returns MACHINE1.

Viewing 10 posts - 1 through 9 (of 9 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

You want the client name?

Because your query is run on Machine2, so Machine2 need to see your Machine as "originator" to query to run?

N 56°04'39.16"
E 12°55'05.25"

Mark Cowne One Orange Chip Points: 26977 More actions · Answer 2

Maybe this?

select hostname from master.dbo.sysprocesses where spid=@@spid

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

Have a look at the CONNECTIONPROPERTY function

SELECTCONNECTIONPROPERTY('client_net_address')

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Or from sys.dm_exec_connections

N 56°04'39.16"
E 12°55'05.25"

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 5

Step 1 :

sp_configure 'allow updates',1

Reconfigure with override

sp_configure 'show advanced options',1

Reconfigure with override

sp_configure 'xp_cmdshell',1

Reconfigure with override

step 2:

create proc localservname

as

create table #test (rownum int identity (1,1),servname nvarchar(100))

insert into test exec xp_cmdshell 'ping localhost -n 1'

select substring(servname,8,12) from test where rownum = 2

drop table #test

You need to modify it a bit as per server name length .

Might be leanthy for you ...

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 6

I think Mark's solution should do .

Regards

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

PNS Right there with Babe Points: 773 More actions · Answer 7

PNS

Right there with Babe

Points: 773

June 29, 2009 at 5:22 am

#1016722

is this you looking for?

select host_name()

Pandian S SSChasing Mays Points: 625 More actions · Answer 8

-- It will produce ServerName/Instance Name

SELECT HOST_NAME() 'Client Name',SERVERPROPERTY('SERVERNAME') 'DB Server Name'

--OR

-- It will produce ServerName

SELECT HOST_NAME() 'Client Name',SERVERPROPERTY('MACHINENAME') 'DB Server Name'

thegreatelectron SSC Rookie Points: 33 More actions · Answer 9

Thanks for your suggestions, everyone.

The answer turned out to be

select host_name()

Obtaining the name of machine you are running a SQL statement from

Cookies on SQLServerCentral