Solved

Retreive External IP Address

Posted on 2012-03-22
1
324 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

740 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