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 : 1.1.1.1

    server "B" with ip :1.1.1.2

    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 1.1.1.2

    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:

    nslookup 123.45.67.89

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

    As

    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

    Go

    -- Usage example

    Declare @machineName varchar(80)

    exec sp_usr_LookUpNameFromIP '123.45.67.89', @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 system....it'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?

    Thanks!

    • 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