April 27, 2012 at 2:58 pm
I was asked to create a centralized location that contains various bits of information about all of my servers, and as it turns out my company still has around 20 SQL Server 2000 instances. One of the pieces of information that I need to pull weekly is the IP address of the server. Now for the SQL Server 2005 and 2008 machines I used:
SELECT local_net_address
FROM sys.dm_exec_connections
WHERE session_id=@@SPID
Does anyone know of a simple query that would pull the server IP and would work on SQL 2000 machines?
Thanks.
April 30, 2012 at 6:58 am
Forgot to mention when I posted this that I was able to find a way to pull the IP address by creating a stored procedure on each server. This option would work for my purpose but I am kind of holding it as a last resort for if I cannot find another way.
April 30, 2012 at 7:18 am
you want to think outside of the box on this one...literally.
you do not want to connect to a sql server simply to ask the sqlserver what it's IP is...since you already know the name of it(since you connected to it!) you can do this from a command line.
if you have 20 installs, you'd need to connect 20 times, and there might be a 21st installation out there somewhere. Instead you want to do it at a higher level.
I would suggest looking at either of these two free tools to help you insead:
SQL Ping3..0 or above (which I cannot find a valid link for anymore) or SQL Recon[/url]
both of which will inventory ALL SQL Servers that exist on the network, and figure out what version of SQL is installed
Lowell
May 1, 2012 at 11:00 am
Try using this query:
EXEC Master..xp_cmdshell 'ipconfig'
If xp_cmdshell access is disabled on the server you will need to enable it.
However I like the idea of using SQLPing.
May 2, 2012 at 7:02 am
Not saying this is the smoothest code, but it works pretty well. You will need to have xp_cmdshell enabled on the machine you are running this from, but you should only need to run it from the one location.
Build yourself a table with at least Servername (mine was called 'Servers' below)
<<should note i found most of this script elsewhere and tweaked it, but could not find original source to reference>>
----------
Declare @ip varchar(255), @cmd varchar(100)
Declare @ipLine varchar(200)
Declare @pos int,
@Cur Cursor,
@Command varchar(100),
@ServerID int,
@ServerIP varchar(50),
@ServerName varchar(50),
@status varchar(50),
@PingResult varchar(300)
create table #temptb (grabfield varchar(255), ipadd varchar(50), servername varchar(50))
SET NOCOUNT ON
SET @Cur = Cursor for (SELECT ServerID, ServerName FROM Servers)
Open @Cur
FETCH NEXT FROM @Cur INTO @ServerID, @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @cmd = 'ping ' + @ServerName + ' -n 1'
insert into #temptb (grabfield) exec master.dbo.xp_cmdshell @cmd
update #temptb set servername = @ServerName where servername is null
update #temptb set ipadd = substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1) from #temptb where left(grabfield,7) = 'Pinging'
FETCH NEXT FROM @Cur INTO @ServerID, @ServerName
END
select distinct servername,ipadd from #temptb where ipadd is not null order by servername
Close @Cur
Deallocate @Cur
drop table #temptb
May 2, 2012 at 2:36 pm
You guys have all been a great help. I think the response that uk00121 is going to work out best for what I'm being asked to do. Since our standard security is to not have xp_cmdshell enabled the method posed by D Gillespie was not ideal, but I should have no problem convincing my employer to deviate from our standard on one instance. Thanks again for the help everyone!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply