Solved

Retreive External IP Address

Posted on 2012-03-22
1
323 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 500 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

830 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