Server IP Address

  • 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.

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • 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

  • 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