About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, February 15, 2013

GetIP


Get IP address from T-SQL. 

Requires xp_cmdshell to be enabled.  If this is a problem for your environment, consider enabling it, run the proc, then disable it.

NOTE:  if there is more than one IP address, the output will indicate that:
                10.1.10.92 (IPs found: 2)

In this event, you will need to add some code to differentiate which one you need.

Use Admin
go

IF Object_id('dbo.GetIP') Is Not Null
      DROP PROC dbo.GetIP
go

CREATE PROC [dbo].[GetIP]
      @IP varchar(30) OUTPUT,
      @Debug bit = 0
AS
/*    DATE        AUTHOR            REMARKS
      2/15/13           PPaiva            Initial creation.
     
      USAGE
            -- Get IP
            DECLARE @ip varchar(30)
            Exec GetIP @ip OUTPUT
            SELECT @ip IP

            -- Get IP with debug
            DECLARE @ip varchar(30)
            Exec GetIP @ip OUTPUT, 1
            SELECT @ip IP
     

      DEBUG
            --Exec sp_configure 'xp_cmdshell', 1
            --reconfigure

            SELECT *
            FROM ServerInfo
     

*/
SET NOCOUNT ON

DECLARE @s varchar(100),
            @NumIPs int

SET @s = 'ipconfig'

CREATE TABLE #ipconfig(
      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
      Col1 varchar(1000)
      )


INSERT INTO #ipconfig
Exec xp_cmdshell @s

DECLARE @PosColon int

IF @Debug = 1
      SELECT *
      FROM #ipconfig
     

DELETE
FROM #ipconfig
WHERE Col1 Not Like '%ipv4%'
   OR Col1 Is Null

SELECT @NumIPs = Count(*)
FROM #ipconfig


SELECT TOP 1 @PosColon = CharIndex(':', Reverse(Col1))
FROM #ipconfig
ORDER BY ID desc

SELECT TOP 1 @ip = LTrim(Right(Col1, @PosColon - 1))
FROM #ipconfig
ORDER BY ID desc

IF @NumIPs > 1
      SET @ip = @ip + ' (IPs found: ' + Convert(varchar, @NumIPs) + ')'


IF @Debug = 1
      SELECT @PosColon 'PosColon (reverse)'
     
      

1 comment:

  1. Hey very nice blog!!
    Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!
    http://sqlservermasters.com/

    ReplyDelete