Retriving host IP Address via T-SQL

  • Guys/Gal,

    I need help, I am going nuts... I am trying to find a query which will retrieve the IP-Address of the local host... It seems that SQL does not store this anywhere!!!

    Any help would be welcome...

    Thank You...

  • See, if this helps:

    set nocount on

    declare @ip varchar(255), @cmd varchar(100)

    set @cmd = 'ping ' + HOST_NAME()

    create table #temptb (grabfield varchar(255))

    insert into #temptb exec master.dbo.xp_cmdshell @cmd

    select @ip = substring(grabfield,  charindex('[',grabfield)+1,

     charindex(']',grabfield)-charindex('[',grabfield)-1) from #temptb  where left(grabfield,7) = 'Pinging'

    print @ip

    drop table #temptb

    set nocount off

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Just to add to the great code from Frank you may want to change the 'ping ' to 'ping -n 1 '.  This will provide 1 ping, 1 ping only Mr verselli (sorry Red October moment).  Seriously though, DEFAULT # of pings = 4, this will send 1x ping ....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • Doh, thanks for this -n 1 !!!

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Nice one Frank

    I was using xp_cmdshell 'ipconfig' (which is a bit faster) to check the general ip settings but 'ping ' + HOST_NAME() is perhaps easier to hack.

    Max

  • Thanks! There is more than one way to skin that cat. However, native T-SQL support for this is on my personal wishlist.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • LOL

    Max

  • Thanks everyone, it works like a charm....

     

     

  • host_name() will return workstation IP address, the computer that calls this code. In order to get server IP, you should stick to ipconfig

  • Hm, why only does HOST_NAME() return my workstation name?

    select host_name()

                        

    --------------------

    WST3XXXXXX

    (1 row(s) affected)

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • select Host_name() returns the local machine name.

    select CAST(SERVERPROPERTY('servername') as nvarchar(255)) always returns the server host name.

    you can try this in query analyzer from local server and remote client.

     

     

  • I use this.

    It gives me not only the ip-address, but also the domain-suffix for the server

     CREATE TABLE #tIpInfo (vlgnr smallint not null identity primary key, IpAdres varchar(500))

    declare @DosCmd varchar(100)

     if charindex('\',@@servername) > 0

        begin

      select @DosCmd = 'ping ' + substring(@@servername,1,charindex('\',@@servername) - 1)  + ' -n 1 '

        end

     else

        begin

      select @DosCmd = 'ping ' + @@servername + ' -n 1 '

        end

     insert into #tIpInfo  (IpAdres)

      exec master.dbo.xp_cmdshell @DosCmd

    select  case when charindex('.',IpAdres) < charindex('[',IpAdres) then substring(IpAdres,  charindex('.',IpAdres)  , charindex('[',IpAdres) - charindex('.',IpAdres) - 1)

        else 'NoDNS'

        end as DNS_Suffix

     , substring(IpAdres,  charindex('[',IpAdres) + 1 , charindex(']',IpAdres) - charindex('[',IpAdres) - 1) as IpAdres

      from #tIpInfo Ip

     where Ip.IpAdres like 'Pinging%'

    drop table #tIpInfo

    Have a nice weekend

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Great code...I changed HOST_NAME() to @@servername and got the IP of the server. Works great.

  • In some cases, the @@servername and serverproperty('servername') does not always return the same result. It's safe to use serverproeprty('servername') becuase The 'servername' server property automatically reports changes in the network name of the computer but @@servername does not. @@servername reports the changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

     

  • In order to be 'complete' about the networkaddress, you also need to know the subnetmask associated to the particular ip address.

    Here's a quickie using ipconfig to get both those values.

    create table #ipconfig

    ( conf varchar(50) null )

    insert #ipconfig

    exec master..xp_cmdshell 'ipconfig'

    declare @ip varchar(15), @mask varchar(15)

    select  @ip = right(conf, charindex(':', reverse(conf)) -1)

    from  #ipconfig

    where  patindex('%IP Address%', conf) > 0

     

    select @mask = right(conf, charindex(':', reverse(conf)) -1)

    from  #ipconfig

    where patindex('%Subnet Mask%', conf)> 0

    select  ltrim(@ip)  as ip,

     ltrim(@mask)  as mask

    drop table #ipconfig

    go

    /Kenneth

     

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

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