how to find the IP address through instance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to find the IP address through instance

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-11-13 : 04:08:42

Dear All,
i used to connect to different instances by their instance names. is it possible to know the Ip address of that instance through a query?

Arnav
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-13 : 04:50:41

All instances on same server has same ip.

E 12°55'05.63"
N 56°04'39.26"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-11-13 : 05:53:06

no...
i'm talking about different servers. even some local servers also having different names...
ex: my server IP's are (122.168.1.12 , 122.168.1.13, 122.168.1.14 ....like that) and the instance names for those server are
(prod1, prod2, prod3 ....like that...)
like this around 45 servers are there. i used to connect through SSMS with instance name.

now
is it possible to know the Ip address of that instance through a query?

thanx in advance

Arnav
Even you learn 1%, Learn it with 100% confidence.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 05:58:41

You'd have use xp_cmdshell for this.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 06:02:06

Something like ,

Declare @pos int
set nocount on
-- set @ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select ipLine
from #temp
where ipLine like '%IP ADDRESS%'

And you should avoid giving out IP addresses in forums.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-11-13 : 06:09:14

Great...
thank you very much sakets

Arnav
Even you learn 1%, Learn it with 100% confidence.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 06:21:59

quote:
Originally posted by sunsanvin

Great...
thank you very much sakets

Arnav
Even you learn 1%, Learn it with 100% confidence.

np.
btw, Got this proc which returns IP. Uses the same logic as above.

create Procedure sp_get_ip_address (@ip varchar(40) out)
as
begin
Declare @ipLine varchar(200)
Declare @pos int
set nocount on
set @ip = NULL
Create table #temp (ipLine varchar(200))
Insert #temp exec master..xp_cmdshell 'ipconfig'
select @ipLine = ipLine
from #temp
where upper (ipLine) like '%IP ADDRESS%'
if (isnull (@ipLine,'***') != '***')
begin
set @pos = CharIndex (':',@ipLine,1);
set @ip = rtrim(ltrim(substring (@ipLine ,
@pos + 1 ,
len (@ipLine) - @pos)))
end
drop table #temp
set nocount off
end
go

declare @ip varchar(40)
exec sp_get_ip_address @ip out
print @ip

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources