View SQL Server information using Transact-SQL script

July 4, 2012

Recently one of our clients requested information about their SQL Server infrastructure which we manage. In order to complete this task, I’ve written the following Transact-SQL script to retrieve information about SQL Server infrastructure.

This script is compatible with SQL Server 2005 and above:

SET NOCOUNT ON;

DECLARE  @SQLServerStartupMode [int]
        ,@SQLAgentStartupMode [int]
        ,@LoadID [int]
        ,@Position [int]
        ,@LoginMode [int]
        ,@SQLServerAuditLevel [int]
        ,@SQLServerStartupType [char](12)
        ,@SQLAgentStartupType [char](12)
        ,@SQLServerServiceAccount [varchar](64)
        ,@SQLAgentServiceAccount [varchar](64)
        ,@SQLServerRegistryKeyPath [varchar](256)
        ,@SQLAgentRegistryKeyPath [varchar](256)
        ,@InstanceName [nvarchar](128) 
        ,@FullInstanceName [nvarchar](128) 
        ,@SystemInstanceName [nvarchar](128) 
        ,@ErrorLogDirectory [nvarchar](128)
        ,@Domain [nvarchar](64)
        ,@IPLine [nvarchar](256)
        ,@IpAddress [nvarchar](16)
        ,@ActiveNode [nvarchar](128)
        ,@AuthenticationMode [varchar](64)
        ,@PortNumber [varchar](8) 
        ,@PageFile [varchar](124)
        ,@ClusterNodes [nvarchar](32) 
        ,@BinariesPath [nvarchar](128) 
        ,@RegistryKeyPath [nvarchar](256) 
        ,@RegistryPath1 [nvarchar](256) 
        ,@RegistryPath2 [nvarchar](256) 
        ,@RegistryPath3 [nvarchar](256) 
        ,@SQLServerInstallationLocation [nvarchar](512)

IF OBJECT_ID('[Tempdb].[dbo].[#_IPCONFIG_OUTPUT]') IS NOT NULL 
DROP TABLE [dbo].[#_IPCONFIG_OUTPUT]

IF OBJECT_ID('[Tempdb].[dbo].[#_PAGE_FILE_DETAILS]') IS NOT NULL 
DROP TABLE [dbo].[#_PAGE_FILE_DETAILS]

IF OBJECT_ID('[Tempdb].[dbo].[#_XPMSVER]') IS NOT NULL 
DROP TABLE [dbo].[#_XPMSVER]

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] 
            WHERE [name] = '##_SERVER_CONFIG_INFO' 
            AND [type] IN (N'U'))
DROP TABLE [dbo].[##_SERVER_CONFIG_INFO]

CREATE TABLE [dbo].[#_PAGE_FILE_DETAILS] ([data] [varchar](500))

CREATE TABLE [dbo].[#_IPCONFIG_OUTPUT] ([IPConfigCommandOutput] [nvarchar](256))

CREATE TABLE [dbo].[#_XPMSVER]([IDX] [int] NULL
,[C_NAME] [varchar](100) NULL
,[INT_VALUE] [float] NULL
,[C_VALUE] [varchar](128) NULL ) ON [PRIMARY]

CREATE TABLE [dbo].[##_SERVER_CONFIG_INFO](
    [Domain] [nvarchar](64) NULL,
    [SQLServerName] [varchar](64) NULL,
    [InstanceName] [nvarchar](128) NULL,
    [ComputerNamePhysicalNetBIOS] [nvarchar](128) NULL,
    [IsClustered] [varchar](13) NULL,
    [ClusterNodes] [nvarchar](32) NULL,
    [ActiveNode] [nvarchar](128) NULL,
    [HostIPAddress] [nvarchar](16) NULL,
    [PortNumber] [varchar](8) NULL,
    [IsIntegratedSecurityOnly] [varchar](64) NULL,
    [AuditLevel] [varchar](38) NOT NULL,
    [ProductVersion] [varchar](100) NULL,
    [ProductLevel] [varchar](100) NULL,
    [ResourceVersion] [varchar](100) NULL,
    [ResourceLastUpdateDateTime] [varchar](100) NOT NULL,
    [EngineEdition] [varchar](64) NULL,
    [BuildClrVersion] [varchar](100) NOT NULL,
    [Collation] [varchar](100) NULL,
    [CollationID] [varchar](100) NULL,
    [ComparisonStyle] [varchar](100) NULL,
    [IsFullTextInstalled] [varchar](26) NULL,
    [SQLCharset] [varchar](100) NOT NULL,
    [SQLCharsetName] [varchar](100) NOT NULL,
    [SQLSortOrderID] [varchar](100) NOT NULL,
    [SQLSortOrderName] [varchar](100) NOT NULL,
    [Platform] [varchar](128) NULL,
    [FileDescription] [varchar](128) NULL,
    [WindowsVersion] [varchar](128) NULL,
    [ProcessorCount] [float] NULL,
    [ProcessorType] [varchar](128) NULL,
    [PhysicalMemory] [float] NULL,
    [ServerPageFile] [varchar](124) NULL,
    [SQLInstallationLocation] [nvarchar](512) NULL,
    [BinariesPath] [nvarchar](128) NULL,
    [ErrorLogsLocation] [nvarchar](128) NULL,
    [MSSQLServerServiceStartupUser] [varchar](64) NULL,
    [MSSQLAgentServiceStartupUser] [varchar](64) NULL,
    [MSSQLServerServiceStartupType] [char](12) NULL,
    [MSSQLAgentServiceStartupType] [char](12) NULL,
    [InstanceLastStartDate] [datetime] NULL,
    [LoadID] [int]) ON [PRIMARY]

------ Finding SQL Server and Agent Service Account Information ------
IF SERVERPROPERTY('InstanceName') IS NULL -- Default Instance
 BEGIN --default instance
    SET @SQLServerRegistryKeyPath='SYSTEM\CurrentControlSET\SERVICES\MSSQLSERVER'
    SET @SQLAgentRegistryKeyPath='SYSTEM\CurrentControlSET\SERVICES\SQLSERVERAGENT'
 END
ELSE 
 BEGIN --Named Instance
    SET @SQLServerRegistryKeyPath = 'SYSTEM\CurrentControlSET\SERVICES\MSSQL$'
    + CAST (SERVERPROPERTY('InstanceName') AS [sysname])

    SET @SQLAgentRegistryKeyPath = 'SYSTEM\CurrentControlSET\SERVICES\SQLAgent$'
    + CAST (SERVERPROPERTY('InstanceName') AS [sysname]) 
 END

EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
                            ,@SQLServerRegistryKeyPath
                            ,@value_name = 'Start'
                            ,@value = @SQLServerStartupMode OUTPUT 

EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
                            ,@SQLAgentRegistryKeyPath
                            ,@value_name = 'Start'
                            ,@value = @SQLAgentStartupMode OUTPUT 

SET @SQLServerStartupType = (SELECT 'Start Up Mode' = 
                                CASE 
                                WHEN @SQLServerStartupMode = 2 THEN 'Automatic' 
                                WHEN @SQLServerStartupMode = 3 THEN 'Manual' 
                                WHEN @SQLServerStartupMode = 4 THEN 'Disabled' 
                                END)

SET @SQLAgentStartupType = (SELECT 'Start Up Mode' = 
                                CASE 
                                WHEN @SQLAgentStartupMode = 2 THEN 'Automatic' 
                                WHEN @SQLAgentStartupMode = 3 THEN 'Manual' 
                                WHEN @SQLAgentStartupMode = 4 THEN 'Disabled' 
                                END) 

EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
                            ,@SQLServerRegistryKeyPath
                            ,@value_name = 'ObjectName'
                            ,@value = @SQLServerServiceAccount OUTPUT 

EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
                            ,@SQLAgentRegistryKeyPath
                            ,@value_name = 'ObjectName'
                            ,@value = @SQLAgentServiceAccount OUTPUT 

------ Reading registry keys for Binaries, Errorlogs location and Domain ------
SET @InstanceName = COALESCE (CONVERT([nvarchar](100)
                        , SERVERPROPERTY('InstanceName')), 'MSSQLSERVER'); 
IF @InstanceName != 'MSSQLSERVER' 
 BEGIN
    SET @InstanceName = @InstanceName 
 END

SET @FullInstanceName = COALESCE (CONVERT([nvarchar](100)
                            , SERVERPROPERTY('InstanceName')), 'MSSQLSERVER'); 
IF @FullInstanceName != 'MSSQLSERVER' 
 BEGIN 
    SET @FullInstanceName = 'MSSQL$'+ @FullInstanceName 
 END

EXEC [master]..[xp_regread]  N'HKEY_LOCAL_MACHINE'
                            ,N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'
                            ,@InstanceName
                            ,@SystemInstanceName OUTPUT; 

SET @RegistryKeyPath = N'SYSTEM\CurrentControlSET\Services\' 
    + @FullInstanceName;

SET @RegistryPath1 = N'Software\Microsoft\Microsoft SQL Server\' 
    + @SystemInstanceName + '\MSSQLServer\Parameters'; 

SET @RegistryPath2 = N'Software\Microsoft\Microsoft SQL Server\' 
    + @SystemInstanceName + '\MSSQLServer\supersocketnetlib\TCP\IP1';

SET @RegistryPath3 = N'SYSTEM\ControlSET001\Services\Tcpip\Parameters\'; 

IF @RegistryPath1 IS NULL 
 BEGIN 
    SET @InstanceName = COALESCE(CONVERT([nvarchar](100)
                    ,SERVERPROPERTY('InstanceName')), 'MSSQLSERVER');
 END 

EXEC [master]..[xp_regread]  N'HKEY_LOCAL_MACHINE'
                            ,N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL'
                            ,@InstanceName
                            ,@SystemInstanceName OUTPUT; 

EXEC [master]..[xp_regread]  N'HKEY_LOCAL_MACHINE'
                            ,@RegistryKeyPath
                            ,@value_name = 'ImagePath'
                            ,@value = @BinariesPath OUTPUT 

EXEC [master]..[xp_regread]  N'HKEY_LOCAL_MACHINE'
                            ,@RegistryPath1
                            ,@value_name = 'SQLArg1'
                            ,@value = @ErrorLogDirectory OUTPUT 

EXEC [master]..[xp_regread]  N'HKEY_LOCAL_MACHINE'
                            ,@RegistryPath3
                            ,@value_name = 'Domain'
                            ,@value = @Domain OUTPUT 

SELECT @ClusterNodes = COALESCE(@ClusterNodes+', ' ,'') + [Nodename] 
FROM [sys].[dm_os_cluster_nodes]

IF @ClusterNodes IS NULL
 BEGIN
    SET @ClusterNodes = 'Not Clustered' 
 END

SET @InstanceName = CONVERT([varchar](25), SERVERPROPERTY('InstanceName'))

EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE'
                                    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
                                    ,N'AuditLevel'
                                    ,@SQLServerAuditLevel OUTPUT

EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE'
                                    ,N'SOFTWARE\Microsoft\MSSQLServer\Setup'
                                    ,N'SQLPath'
                                    ,@SQLServerInstallationLocation OUTPUT

------ Finding IP Address ------
INSERT #_IPCONFIG_OUTPUT EXEC [master]..[xp_cmdshell] 'ipconfig'

IF LEFT (CAST(SERVERPROPERTY('ProductVersion')AS [sysname]), 5) = '10.50'
 BEGIN
    SELECT @IPLine = [IPConfigCommandOutput] 
    FROM #_IPCONFIG_OUTPUT
    WHERE UPPER ([IPConfigCommandOutput]) LIKE '%IPv4 Address%'

    IF (ISNULL(@IPLine,'***') != '***')
    BEGIN
        SET @Position = CharIndex (':', @IPLine, 1);
        SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine 
            ,@Position + 1 ,LEN(@IPLine) - @Position)))
    END 
END
ELSE
BEGIN
    SELECT @IPLine = [IPConfigCommandOutput] 
    FROM #_IPCONFIG_OUTPUT
    WHERE UPPER ([IPConfigCommandOutput]) LIKE '%IP Address%'

    IF (ISNULL(@IPLine,'***') != '***')
    BEGIN
        SET @Position = CharIndex (':', @IPLine, 1);
        SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine
            ,@Position + 1,LEN(@IPLine) - @Position)))
    END 
END

------ Finding Port Information ------ 
IF @InstanceName IS NULL
BEGIN
    SET @RegistryKeyPath = 'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\' 
END
ELSE
BEGIN
    SET @RegistryKeyPath = 'Software\Microsoft\Microsoft SQL Server\' 
                + @InstanceName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
END

EXEC [master]..[xp_regread] 'HKEY_LOCAL_MACHINE'
                            ,@RegistryKeyPath
                            ,@value_name = 'tcpPort'
                            ,@value = @PortNumber OUTPUT -- Port Number

------ Finding Authentication Mode ------
EXEC [master]..[xp_instance_regread] N'HKEY_LOCAL_MACHINE'
                                    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
                                    ,@value_name = N'LoginMode' 
                                    ,@value = @LoginMode OUTPUT

SET @AuthenticationMode = (SELECT 'AuTHENtication Mode' = 
                            CASE 
                            WHEN @LoginMode = 1 THEN 'Windows Authentication' 
                            WHEN @LoginMode = 2 THEN 'Mixed Mode Authentication' 
                            END ) 

------ Finding Active Node ------
EXEC [master]..[xp_regread]  @rootkey = 'HKEY_LOCAL_MACHINE'
                            ,@RegistryKeyPath = 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName'
                            ,@value_name = 'ComputerName'
                            ,@value = @ActiveNode OUTPUT

INSERT INTO [#_PAGE_FILE_DETAILS] 
EXEC [master]..[xp_cmdshell] 'wmic pagefile list /format:list'

SELECT @PageFile = RTRIM(LTRIM([data])) 
FROM #_PAGE_FILE_DETAILS 
WHERE [data] LIKE 'AllocatedBaseSize%'

INSERT INTO [#_XPMSVER]
EXEC( 'master.dbo.xp_msver')

SELECT 
    UPPER(@Domain) AS [Domain]
    ,CONVERT([varchar](64),SERVERPROPERTY('ServerName')) AS [SQLServerName]
    ,@FullInstanceName AS [InstanceName]
    ,@ActiveNode AS [ComputerNamePhysicalNetBIOS]
    ,(CASE
        WHEN CONVERT([varchar](100),SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered'
        WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
        WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error'
        END) AS [IsClustered]
    ,@ClusterNodes AS [ClusterNodes]
    ,@ActiveNode AS [ActiveNode]
    ,@IPAddress AS [HostIPAddress]
    ,@PortNumber AS [PortNumber]
    ,@AuthenticationMode AS [IsIntegratedSecurityOnly]
    ,(CASE 
        WHEN @SQLServerAuditLevel = 0 THEN 'None.'
        WHEN @SQLServerAuditLevel = 1 THEN 'Successful Logins Only'
        WHEN @SQLServerAuditLevel = 2 THEN 'Failed Logins Only'
        WHEN @SQLServerAuditLevel = 3 THEN 'Both Failed and Successful Logins Only'
        ELSE 'N/A' END) AS [AuditLevel]
    ,CONVERT([varchar](100),SERVERPROPERTY('ProductVersion')) AS [ProductVersion]
    ,CONVERT([varchar](100),SERVERPROPERTY('ProductLevel')) AS [ProductLevel]
    ,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('ResourceVersion'))
    ,CONVERT([varchar](100),SERVERPROPERTY('ProductVersion'))) AS [ResourceVersion]
    ,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('ResourceLastUpdateDateTime')) 
        ,'Information Not Available') AS [ResourceLastUpdateDateTime]
    ,CAST (SERVERPROPERTY('Edition') as [varchar](64)) AS [EngineEdition]
    ,ISNULL(CONVERT([varchar](100),SERVERPROPERTY('BuildClrVersion')), 'NOT Applicable') AS [BuildClrVersion]
    ,CONVERT([varchar](100),SERVERPROPERTY('Collation')) AS [Collation]
    ,CONVERT([varchar](100),SERVERPROPERTY('CollationID')) AS [CollationID]
    ,CONVERT([varchar](100),SERVERPROPERTY('ComparisonStyle')) AS [ComparisonStyle]
    ,(CASE
        WHEN CONVERT([varchar](100),SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed'
        WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed'
        WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error'
        END) AS [IsFullTextInstalled]
    ,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlCharSet')), 'No Information') AS [SQLCharset]
    ,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlCharSetName')), 'No Information') AS [SQLCharsetName]
    ,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlSortOrder')), 'No Information') AS [SQLSortOrderID]
    ,ISNULL (CONVERT([varchar](100), SERVERPROPERTY('SqlSortOrderName')), 'No Information') AS [SQLSortOrderName]
    ,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'Platform') as [Platform]
    ,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'FileDescription' ) as [FileDescription]
    ,(SELECT C_VALUE from [#_XPMSVER] where [C_NAME] = 'WindowsVersion') as [WindowsVersion] 
    ,(SELECT INT_VALUE from [#_XPMSVER] where [C_NAME] = 'ProcessorCount') as [ProcessorCount] 
    ,(SELECT ISNULL(C_VALUE,CAST (INT_VALUE AS VARCHAR(9))) from #_XPMSVER where [C_NAME] = 'ProcessorType') as [ProcessorType] 
    ,(SELECT INT_VALUE from [#_XPMSVER] where [C_NAME] = 'PhysicalMemory') as [PhysicalMemory]
    ,@PageFile AS [ServerPageFile] 
    ,@SQLServerInstallationLocation AS [SQLInstallationLocation]
    ,@BinariesPath AS [BinariesPath] 
    ,@ErrorLogDirectory AS [ErrorLogsLocation] 
    ,@SQLServerServiceAccount AS [MSSQLServerServiceStartupUser]
    ,@SQLAgentServiceAccount AS [MSSQLAgentServiceStartupUser]
    ,@SQLServerStartupType AS [MSSQLServerServiceStartupType]
    ,@SQLAgentStartupType AS [MSSQLAgentServiceStartupType]
    ,(SELECT [login_time] FROM [master]..[sysprocesses] WHERE [spid] = 1) AS [InstanceLastStartDate]

-- Dropping temporary table
IF OBJECT_ID('[Tempdb].[dbo].[#_IPCONFIG_OUTPUT]') IS NOT NULL 
DROP TABLE [dbo].[#_IPCONFIG_OUTPUT]

IF OBJECT_ID('[Tempdb].[dbo].[#_PAGE_FILE_DETAILS]') IS NOT NULL 
DROP TABLE [dbo].[#_PAGE_FILE_DETAILS]

IF OBJECT_ID('[Tempdb].[dbo].[#_XPMSVER]') IS NOT NULL 
DROP TABLE [dbo].[#_XPMSVER]
GO

I hope you find it useful!

About these ads

From → SQL Scripts

6 Comments

Sam Jazz permalink

I had to modify one line in the final SELECT

was :
,@PageFile AS [ServerPageFile]

is now:
,REPLACE(REPLACE(@PageFile, CHAR(13) , ”), CHAR(10), ”) AS [ServerPageFile]

Reply
Lee Linares permalink

Excellent routine. However, I would get NULL for IP Address most of the time. The line of code:

IF LEFT (CAST(SERVERPROPERTY(‘ProductVersion’)AS [sysname]), 5) = ’10.50′

Would only execute if the instance was SQL 2008 R2. Also will not execute the SQL instance was installed on a Windows 2003 server.

I populated [#_XPMSVER] before the section to find IP address, added a variable for Windows version and used it in the IF statement instead.

INSERT INTO [#_XPMSVER]
EXEC( ‘master.dbo.xp_msver’)

SELECT @OS = C_VALUE from [#_XPMSVER] where [C_NAME] = ‘WindowsVersion’

–IF LEFT (CAST(SERVERPROPERTY(‘ProductVersion’)AS [sysname]), 5) = ’10.50′
IF LEFT (@OS, 2) = ’6.’ — If the OS is Windows 2008 look for IPv4 Address

Thanks again for writing and posting this excellent and very useful routine.

Reply
Mr. Ron Smit permalink

I agree with my fellow commenters, excellent routine. As a matter of fact, we use a routine with a similar purpose, which yields much of the same info, however, I did find quite a few useful additions in your routine, which I copied into our own routine (citing your name as the source, of course).

With regards to the domain, I think there is a bug in your code. (Mind you, I’m not a Windows registry guru, so my suggestion to remedy it may also not be fully correct, but the current approach is buggy, anyway.) Most people will probably not hit it, though, but I was fortunate to run into it, thereby discovering the bug.

In order to determine the network domain, you query the registry key @RegistryPath which translates to[SYSTEM\ControlSET001\Services\Tcpip\Parameters\]. However, it is quite possible for that registry key NOT to exist. For instance, in our clustered environment I encountered ControlSet002 and 003 and 004 but NOT 001. However, there should be no need to query any of the ControlSet00n keys, because we can just query the *CURRENT*ControlSet, which should always exist. Hence, the full key under HKEY_LOCAL_MACHINE then becomes [SYSTEM\CurrentControlSet\Services\Tcpip\Parameters].

Or to put it ultrashort:
change ControlSet001 to CurrentControlSet

Thanks for all the good ideas and for sharing these!

Reply
Mr Ron Smit permalink

Change

SET @RegistryPath3 = N’SYSTEM\ControlSET001\Services\Tcpip\Parameters\’;

to

SET @RegistryPath3 = N’SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\’;

because it might be that ControlSet001 (or indeed any other ControlSet00n) does not exist

Reply
Ivan Cruz permalink

@Lee Linares, No need to populate table beforehand! Left a comment, just use this..
—— Finding IP Address ——
INSERT #_IPCONFIG_OUTPUT EXEC [master]..[xp_cmdshell] ‘ipconfig’

IF LEFT (CAST(SERVERPROPERTY(‘ProductVersion’)AS [sysname]), 5) = ’10.50′
BEGIN
SELECT @IPLine = [IPConfigCommandOutput]
FROM #_IPCONFIG_OUTPUT
WHERE UPPER ([IPConfigCommandOutput]) LIKE ‘%IPv4 Address%’

IF (ISNULL(@IPLine,’***’) != ‘***’)
BEGIN
SET @Position = CharIndex (‘:’, @IPLine, 1);
SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine
,@Position + 1 ,LEN(@IPLine) – @Position)))
END
END
ELSE
BEGIN
SELECT @IPLine = [IPConfigCommandOutput]
FROM #_IPCONFIG_OUTPUT
WHERE UPPER ([IPConfigCommandOutput]) LIKE ‘%IPv4 Address%’ –icruz: Changed ‘%IP Address%’ to ‘%IPv4 Address%’

IF (ISNULL(@IPLine,’***’) != ‘***’)
BEGIN
SET @Position = CharIndex (‘:’, @IPLine, 1);
SET @IPAddress = RTRIM(LTRIM(SUBSTRING(@IPLine
,@Position + 1,LEN(@IPLine) – @Position)))
END
END

Reply
Ivan Cruz permalink

In find IP part..

Reply

M	T	W	T	F	S	S
« Jun				Aug »
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

View SQL Server information using Transact-SQL script

Leave a Reply Cancel reply

Basit A. Masood-Al-Farooq

Post Calendar

Recent Posts

Top 5 Posts

My Twitter Feed

Email Subscription

Search this blog!

Copyright

SQLServerPedia.com

SQLServerCentral.com

BeyondRelational.com

Disclaimer

Blog Stats

Follow “Basit's SQL Server Tips”

View SQL Server information using Transact-SQL script

Rate this:

Share this:

Like this:

Leave a Reply Cancel reply

Basit A. Masood-Al-Farooq

Post Calendar

Category Cloud

Recent Posts

Top 5 Posts

My Twitter Feed

Email Subscription

Search this blog!

Copyright

SQLServerPedia.com

SQLServerCentral.com

BeyondRelational.com

Disclaimer

Blog Stats

Follow “Basit's SQL Server Tips”