Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Check Instance Info


Check Instance Info

Author
Message
Pedro Lopes MS
Pedro Lopes MS
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 144
Comments posted to this topic are about the item Check Instance Info
tskelley
tskelley
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 1154
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
Ed Lyons
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 98
The bottom portion of the code listing appears 'chopped off'.w00t



SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4311
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
Pedro Lopes MS
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
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
SQLQuest29
SQLQuest29
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 4311
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 :-)
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8706 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search