Post reply

how to get server name from IP address

  • February 6, 2007 at 7:55 am

    #175062

    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.

  • February 6, 2007 at 8:35 am

    #688062

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

  • February 6, 2007 at 8:40 am

    #688063

    how can i use that?

    cant i use a function or query?

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

  • February 6, 2007 at 8:50 am

    #688067

    from the command line:

    nslookup 123.45.67.89

  • February 6, 2007 at 8:55 am

    #688068

    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.

  • February 6, 2007 at 12:10 pm

    #688126

    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.

  • February 6, 2007 at 8:40 pm

    #688181

    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

  • February 7, 2007 at 12:11 am

    #688203

    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.

  • February 7, 2007 at 2:22 am

    #688216

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

  • February 7, 2007 at 2:31 am

    #688218

    ah ok

    big thanks again

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

  • April 30, 2021 at 11:28 am

    #3877801

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

    Can anyone advise?

    Thanks!

    • This reply was modified 3 years, 7 months ago by  vr.

  • April 30, 2021 at 3:42 pm

    #3877915

    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)

  • July 14, 2021 at 6:29 am

    #3907666

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

  • November 15, 2021 at 6:34 pm

    This was removed by the editor as SPAM

  • January 29, 2022 at 9:32 am

    This was removed by the editor as SPAM

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

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