Check Instance Info

  • Pedro Lopes MS

    SSChasing Mays

    Points: 612

    Comments posted to this topic are about the item Check Instance Info

  • tskelley

    SSCommitted

    Points: 1870

    Thanks Pedro, this is a handy little piece of code. Previously, I would check the error log for the port number that my instance is listening on, which was a slightly tedious process and sometimes not very practical, especially if I needed the information on another SQL server and did not have access to the server files. I actually wrapped it in a sproc and tied it into a shortcut key for quick access (see below):

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CheckThisInstance]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[sp_CheckThisInstance]

    GO

    create proc dbo.sp_CheckThisInstance

    AS

    GO

  • Ed Lyons

    SSC Enthusiast

    Points: 146

    The bottom portion of the code listing appears 'chopped off'.:w00t:

  • SQLQuest29

    SSCrazy Eights

    Points: 8264

    Pedro Lopes MS (10/21/2007)


    Comments posted to this topic are about the item <A HREF="/scripts/61297/">Check Instance Info</A>

    Very nice piece of code.

    This will not report correct IP value on 2008R2 .. You need to add this

    DELETE #blat WHERE CHARINDEX('IP Address', Line)<=0 and CHARINDEX('IPv4 Address', Line)<=0

    Below is the code that works on both 2003 and 2008R2 servers:

    DECLARE @RegKey NVARCHAR(255), @RegKey2 NVARCHAR(255), @RegKey3 NVARCHAR(255), @ListeningOnPort VARCHAR(10), @IP VARCHAR(15)

    DECLARE @SERVER NVARCHAR(12), @GROUP NVARCHAR(12), @NODE NVARCHAR(12), @PNODE NVARCHAR(12), @STATUS NVARCHAR(17)

    DECLARE @CMD VARCHAR(255), @CLUSTER NVARCHAR(13), @LEN INT, @ClusName VARCHAR(16), @ClusIP VARCHAR(15)

    IF SERVERPROPERTY('IsClustered') = 1

    BEGIN

    IF LEN(CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128))) > 0

    BEGIN

    SET @RegKey = N'Software\Microsoft\Microsoft SQL Server\' + CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) + N'\MSSQLServer\SuperSocketNetLib\Tcp'

    SET @RegKey3 = N'Software\Microsoft\Microsoft SQL Server\' + CAST( SERVERPROPERTY( 'InstanceName') AS NVARCHAR(128)) + N'\Cluster'

    END

    ELSE

    BEGIN

    SET @RegKey = N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'

    SET @RegKey3 = N'Software\Microsoft\MSSQLServer\Cluster'

    END

    SET @RegKey2 = N'Cluster'

    EXEC master..xp_regread N'HKEY_LOCAL_MACHINE' , @RegKey2 , N'ClusterName' , @ClusName OUT

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#TempIP%')

    DROP TABLE #TempIP

    CREATE TABLE #TempIP(RegKey VARCHAR(255), IPVal VARCHAR(15), Data VARCHAR(10))

    INSERT #TempIP EXEC master..xp_regread N'HKEY_LOCAL_MACHINE' , @RegKey3 , N'ClusterIpAddr'

    SELECT @IP = IPVal FROM #TempIP

    DROP TABLE #TempIP

    END

    ELSE BEGIN

    SET @RegKey = N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#blat%')

    DROP TABLE #blat

    CREATE TABLE #blat(Line VARCHAR(255))

    INSERT #blat EXEC master..xp_cmdshell 'ipconfig'

    DELETE #blat WHERE CHARINDEX('IP Address', Line)<=0 and CHARINDEX('IPv4 Address', Line)<=0

    DELETE #blat WHERE Line IS NULL

    SELECT TOP 1 @IP = LTRIM((SUBSTRING(Line, CHARINDEX(':', Line)+1, 255))) FROM #blat

    DROP TABLE #blat

    END

    EXEC master..xp_regread N'HKEY_LOCAL_MACHINE' , @RegKey , N'TcpPort' , @ListeningOnPort OUT

    IF @ListeningOnPort IS NULL

    BEGIN

    IF LEN(CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128))) > 0

    BEGIN

    SET @RegKey = N'Software\Microsoft\Microsoft SQL Server\' + rtrim(ltrim(CAST( SERVERPROPERTY('InstanceName') AS NVARCHAR(128)))) + N'\MSSQLServer\SuperSocketNetLib\Tcp'

    END

    ELSE

    BEGIN

    SET @RegKey = N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'

    END

    EXEC master..xp_regread N'HKEY_LOCAL_MACHINE' , @RegKey , N'TcpPort' , @ListeningOnPort OUT--, NO_OUTPUT

    END

    IF @ClusName IS NOT NULL OR @ClusName <> ''

    BEGIN

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#output%')

    DROP TABLE #output

    CREATE TABLE #output (ID INT IDENTITY(1,1), OUTPUT NVARCHAR(255))

    SET @SERVER=LEFT((SELECT @@SERVERNAME),12)

    SET @CMD= 'cluster /CLUSTER:' + @ClusName + ' GROUP "' + @SERVER +'"'

    INSERT #output EXEC master..xp_cmdshell @CMD

    SELECT @NODE = RIGHT(LEFT(OUTPUT,33),12) FROM #output WHERE ID =5

    SELECT @LEN = LEN(OUTPUT) FROM #output WHERE ID = 5

    SELECT @STATUS = CASE

    WHEN @LEN = 44 THEN LEFT(RIGHT(OUTPUT,7),6)

    WHEN @LEN = 45 THEN RIGHT(OUTPUT,8)

    WHEN @LEN = 54 THEN Ltrim(RIGHT(OUTPUT,17))

    END

    FROM #output WHERE ID = 5

    DROP TABLE #output

    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#prefnodes%')

    DROP TABLE #prefnodes

    CREATE TABLE #prefnodes (ID INT IDENTITY(1,1), OUTPUT NVARCHAR(255))

    SET @SERVER=LEFT((SELECT @@SERVERNAME),12)

    SET @CMD= 'cluster /CLUSTER:' + @ClusName + ' GROUP "' + @SERVER +'" /ListOwners'

    INSERT #prefnodes EXEC master..xp_cmdshell @CMD

    SELECT @PNODE = OUTPUT FROM #prefnodes WHERE ID = 6

    DROP TABLE #prefnodes

    SELECT @@SERVERNAME 'Virtual_Server', @ClusName 'Cluster', @PNODE 'Preferred_Node', @NODE 'Current_Node', @IP 'Server IP', @ListeningOnPort 'Port', @STATUS 'Status', GetDate() 'Date'

    END

    ELSE

    SELECT @@SERVERNAME 'Server', @IP 'Server IP', @ListeningOnPort 'Port', GetDate() 'Date'

    Also it gives this error.. haven't got chance to look at though .. will update this post if I get chance

    (22 row(s) affected)

    (12 row(s) affected)

    (9 row(s) affected)

    RegOpenKeyEx() returned error 2, 'The system cannot find the file specified.'

    Msg 22001, Level 1, State 1

    (1 row(s) affected)

    Edit: found this article which explains the error :

    http://www.kodyaz.com/articles/xp_regread-RegQueryValueEx-returned-error-2-the-system-cannot-find-the-file-specified.aspx

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • Pedro Lopes MS

    SSChasing Mays

    Points: 612

    Thanks for that.

    You know, this code was submitted over 4 years ago. SQLServerCentral just published it again. 🙂

    I'll correct this one and others I've submitted a few years ago ASAP.

    Cheers

  • SQLQuest29

    SSCrazy Eights

    Points: 8264

    Pedro Lopes MS (12/15/2011)


    Thanks for that.

    You know, this code was submitted over 4 years ago. SQLServerCentral just published it again. 🙂

    I'll correct this one and others I've submitted a few years ago ASAP.

    Cheers

    Ah ! That explains the reason ..

    Thanks !

    ______________________________________________________________________________________________________________________________________________________________________________________
    HTH !
    Kin
    MCTS : 2005, 2008
    Active SQL Server Community Contributor 🙂

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply