Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Retriving host IP Address via T-SQL Expand / Collapse
Author
Message
Posted Thursday, December 09, 2004 4:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 25, 2010 12:24 PM
Points: 79, Visits: 17

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

Post #150196
Posted Thursday, December 09, 2004 5:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #150202
Posted Thursday, December 09, 2004 5:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 22, 2011 5:35 AM
Points: 1,758, Visits: 9
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
Post #150204
Posted Thursday, December 09, 2004 5:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
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/
Post #150205
Posted Thursday, December 09, 2004 5:32 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:27 AM
Points: 280, Visits: 353

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
Post #150206
Posted Thursday, December 09, 2004 5:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #150207
Posted Thursday, December 09, 2004 5:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:27 AM
Points: 280, Visits: 353
LOL

Max
Post #150208
Posted Thursday, December 09, 2004 6:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 25, 2010 12:24 PM
Points: 79, Visits: 17

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

 

 

Post #150210
Posted Thursday, December 09, 2004 11:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, February 12, 2007 2:10 PM
Points: 94, Visits: 1
host_name() will return workstation IP address, the computer that calls this code. In order to get server IP, you should stick to ipconfig
Post #150270
Posted Thursday, December 09, 2004 11:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285

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/
Post #150274
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse