Post reply

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

  • 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.

  • June 29, 2009 at 4:44 am

    #1016698

    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"

  • June 29, 2009 at 4:46 am

    #1016701

    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

  • June 29, 2009 at 4:46 am

    #1016702

    Have a look at the CONNECTIONPROPERTY function

    SELECTCONNECTIONPROPERTY('client_net_address')

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

  • June 29, 2009 at 4:47 am

    #1016703

    Or from sys.dm_exec_connections

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

  • June 29, 2009 at 4:49 am

    #1016706

    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)

  • June 29, 2009 at 4:51 am

    #1016709

    I think Mark's solution should do .

    Regards

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

  • June 29, 2009 at 5:22 am

    #1016722

    is this you looking for?

    select host_name()

  • June 29, 2009 at 5:37 am

    #1016732

    -- 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'

  • June 29, 2009 at 9:07 am

    #1016891

    Thanks for your suggestions, everyone.

    The answer turned out to be

    select host_name()

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

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