Retriving host IP Address via T-SQL

Question

Post reply

Retriving host IP Address via T-SQL

Kumaran Govender

Ten Centuries

Points: 1089
More actions
December 9, 2004 at 4:54 am

#162861

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

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 2

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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]

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 4

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

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]

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 6

Max-146500

SSCarpal Tunnel

Points: 4691

December 9, 2004 at 5:37 am

#533170

LOL

Max

Kumaran Govender Ten Centuries Points: 1089 More actions · Answer 7

Kumaran Govender

Ten Centuries

Points: 1089

December 9, 2004 at 6:12 am

#533172

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

herb walles Say Hey Kid Points: 692 More actions · Answer 8

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

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]

peterhe SSChampion Points: 11469 More actions · Answer 10

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.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

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

big boy Old Hand Points: 340 More actions · Answer 12

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

peterhe SSChampion Points: 11469 More actions · Answer 13

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.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 14

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