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)'
     
      

Sunday, February 10, 2013

FixServerName


Many like to use the global variable @@ServerName when you need to provide the instance name of your SQL Server.  However, if the machine has been renamed after SQL Server was installed, this variable will be incorrect – it will have the previous instance name of SQL Server.
ServerProperty('ServerName')) will always return the correct instance name.  But it is a bit clumsy to use because its datatype is sql_variant, so you have to first convert it to char or varchar.

This proc will identify and fix the problem.  A service stop/start is required after running the fix.
Use Admin
go

IF Object_ID('dbo.FixServerName') Is Not Null
      DROP PROC dbo.FixServerName
go

CREATE PROC dbo.FixServerName
      @Debug bit = 1
AS
/*    DATE        AUTHOR            REMARKS
      2/10/13           PPaiva            Initial creation.
     
      DESCRIPTION
            Determines if @@ServerName is the same as ServerProperty('ServerName').
                  If not, then the server has likely been renamed after SQL Server
                  was installed. 
                 
            To fix, run this with @Debug = 0, then stop/start the SQL Server service.
     
      USAGE
            FixServerName           -- Examine only, don't fix
            FixServerName 0         -- Fix
     
*/
DECLARE @ServerNameGlobalVar varchar(50),
            @ServerNameProperty varchar(50),
            @Msg varchar(1000)

SET @ServerNameGlobalVar = @@ServerName + 'h'
SET @ServerNameProperty = Convert(varchar(50), ServerProperty('ServerName'))


IF @ServerNameGlobalVar = @ServerNameProperty
      BEGIN
            SET @Msg = '@@ServerName has the correct value.  You can use @@ServerName whenever you need '
                        + Char(13) + 'to provide the SQL instance name.  Nothing to fix.'
                        + Char(13) + '                    @@ServerName = ' + @ServerNameGlobalVar
                        + Char(13) + '    ServerProperty(''ServerName'') = ' + @ServerNameProperty
            Print @Msg
      END
     
ELSE
      BEGIN
            IF @Debug = 1
                  BEGIN
                        SET @Msg = '@@ServerName does NOT have the correct value. Needs to be fixed.'
                                    + Char(13) + '                    @@ServerName = ' + @ServerNameGlobalVar
                                    + Char(13) + '    ServerProperty(''ServerName'') = ' + @ServerNameProperty
                                    + Char(13) + 'To fix, run the following, then stop/start the SQL Server service.'
                                    + Char(13) + '    Exec FixServerName 0'
                        Print @Msg
                  END                          
                 
            ELSE             
                  BEGIN
                        SET @Msg = 'Exec master.dbo.sp_dropserver ''' + @@ServerName + ''''
                        Print @Msg
                        Exec(@Msg)

                        SET @Msg = 'Exec master.dbo.sp_addserver ''' + @ServerNameProperty + ''', ''local'''
                        Print @Msg
                        Exec(@Msg)
                       
                        SET @Msg = 'Need to stop/start the SQL Server service, or reboot, for change to take effect.'
                        Print ''
                        Print @Msg

                  END
                 
      END