Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Retreive External IP Address

Posted on 2012-03-22
1
Medium Priority
?
330 Views
Last Modified: 2012-03-25
Hi all,
Is there a built in function/stored procedure in sql server that returns the external ip address?
similar to http://www.whatismyip.com

If there isn't one, how do I use http://www.whatismyip.com in sql code?

Many thanks in advance

Programmers
0
Comment
Question by:Programmers
1 Comment
 
LVL 16

Accepted Solution

by:
DcpKing earned 1500 total points
ID: 37752523
Try this code

set nocount on
declare @ip varchar(255), @cmd varchar(100)
set @cmd = 'ping  -n 1 ' + 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

and if you get a really weird set of results you may well be on a virtual box with IPV6, etc.

From outside the box I get
Pinging sys76.tekstlogik.com [192.168.7.59] with 32 bytes of data:
From inside:
Pinging sys76.tekstlogik..com [fe80::c0f3:b3a:9464:39a%11] with 32 bytes of data:

(credit: several posts on http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx from 2004).
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question