Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Check Instance Info Expand / Collapse
Author
Message
Posted Sunday, October 21, 2007 10:54 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 7, 2012 9:04 AM
Points: 84, Visits: 144
Comments posted to this topic are about the item Check Instance Info
Post #413258
Posted Tuesday, December 18, 2007 11:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:08 PM
Points: 40, Visits: 938
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



Post #434628
Posted Thursday, December 15, 2011 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 6, Visits: 82
The bottom portion of the code listing appears 'chopped off'.


Post #1222626
Posted Thursday, December 15, 2011 1:35 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 4, 2014 11:04 AM
Points: 739, Visits: 3,809
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
Post #1222750
Posted Thursday, December 15, 2011 3:48 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 7, 2012 9:04 AM
Points: 84, Visits: 144
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
Post #1222793
Posted Thursday, December 15, 2011 4:20 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 4, 2014 11:04 AM
Points: 739, Visits: 3,809
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
Post #1222801
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse