Post reply

Check Instance Info

  • October 21, 2007 at 10:54 pm

    #176406

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

  • December 18, 2007 at 11:27 pm

    #761292

    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

  • December 15, 2011 at 10:35 am

    #1422105

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

  • December 15, 2011 at 1:35 pm

    #1422210

    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

    ELSEBEGIN

    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 🙂

  • December 15, 2011 at 3:48 pm

    #1422250

    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

  • December 15, 2011 at 4:20 pm

    #1422257

    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 🙂

  • May 11, 2016 at 11:43 am

    #1878340

    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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy