how to get server name from IP address

  • the @@servername function  returns the name of the local server running SQL Server.

    is there a function or sql code that can return the server name by giving it the server ip address ?

    for example:

    i am on server "A" wiht ip :

    server "B" with ip :

    i need to run on server A

    a function or query giving it as parameter the ip address of a server

    thats returns in our casae "B" which is the server name corresponding to ip address

    thanks for ur help

    If something's hard to do, then it's not worth doing.

  • Have you tried shelling out to nslookup and attempting to use that?

  • how can i use that?

    cant i use a function or query?

    If something's hard to do, then it's not worth doing.

  • from the command line:


  • ah ok

    but is there any alternative to use it inside a query?

    i might want to hold the server name in a declared variable inside an sql query

    so i want to do something like nslookup but inside a tsql code

    If something's hard to do, then it's not worth doing.

  • Try it with xp_cmdshell and see what output you get. You should be able to parse it from there, but I can't test it at this time. You'll also want to read up on xp_cmdshell if it's new to you, as there are a few security "gotchas" surrounding it.

    Another possibility is to redirect the output to a text file, and then import and parse that if you find it easier.

  • try something like this:


    Create Procedure sp_usr_LookUpNameFromIP (@IpAddress varchar(32), @machineName varchar(80) output)


    Set NoCount On

    Declare @cmd varchar(32)

    Select @cmd = 'nslookup ' + @IpAddress

    Create Table #shellOP (

     lineText varchar(80)


    Insert #shellOP

    exec master..xp_cmdshell @cmd

    Select @machineName = substring(lineText, 9, len(lineText) - 8) from #shellOP where lineText like 'Name:%'

    Drop Table #shellOP


    -- Usage example

    Declare @machineName varchar(80)

    exec sp_usr_LookUpNameFromIP '', @machineName output

     Select @machineName

  • the procedure worked just fine, i just had to enable xp_cmdshell by using the Surface Area Configuration tool .

    thank u a lot all who helped

    one more question why xp_cmdshell  is recommended to be disabled?

    If something's hard to do, then it's not worth doing.

  • "one more question why xp_cmdshell  is recommended to be disabled?"

    Because it runs under the security priviliges of the SQL Account....and can open a security hole on your server.  Remember in most cases it's not your priviliges that are interacting with the SQL file's the SQL server executing instructions on your behalf.

  • ah ok

    big thanks again

    If something's hard to do, then it's not worth doing.

  • Is anything changed since that time? maybe some much easy way was invented for doing same?

    Can anyone advise?


    • This reply was modified 7 months ago by  vr.
  • Like I said in the other post on a similar subject...

    Can you use xp_CmdShell? I ask because that's still the easiest way whether you end up using "ping" or "nslookup". And, no, xp_CmdShell is NOT the big, bad security violation or even a risk that many make it out to be if used correctly.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden, yep, you're right about it.

  • Thanks to all for this discussion, very useful for me too.

Viewing 14 posts - 1 through 14 (of 14 total)

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