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

Read Registry Expand / Collapse
Author
Message
Posted Wednesday, March 25, 2009 4:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:20 PM
Points: 194, Visits: 334
I need a script to find below information from remote servers:
INSTANCE PORT Number (From Registry)
collation from SERVERPROPERTY
Could any one please help? Many thanks in advance



CDBA
Post #683739
Posted Wednesday, March 25, 2009 5:18 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:49 PM
Points: 75, Visits: 409
I think you should check out xp_cmdshell. You can then create your own exe to read the registery and call it from the shell.




How To Post
Post #683768
Posted Wednesday, March 25, 2009 6:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 12,963, Visits: 32,512
well, if you run the script below, it will read the registry and tell you teh listening port...
i duoubt this will work over a linked server, which is what i assume you mean by a remote server?
CREATE TABLE #GetPort
(
token varchar(100),
value varchar(20))
go

DECLARE @inst varchar(200)
DECLARE @inst1 varchar(100)
--Determine registry path and key
IF(charindex('\',@@servername) > 0)
BEGIN
SELECT @inst = substring(@@servername,charindex('\',@@servername) ,50)
SELECT @inst = 'SOFTWARE\Microsoft\Microsoft SQL Server'+@inst+'\MSSQLServer\SuperSocketNetLib\Tcp'
--SELECT @inst1 = 'TcpDynamicPorts'
SELECT @inst1 = 'TcpPort'
END
ELSE
BEGIN
if SUBSTRING(@@VERSION,23,1) = '7'
begin
SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA'
SELECT @inst1 = 'DefaultServerPort'

end
else
begin
SELECT @inst = 'SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp'
SELECT @inst1 = 'DefaultPort'
end
END

print @inst + '\\\\////'+ @inst1

INSERT #GetPort
EXEC master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

SELECT substring(@@servername,1,25) as ServerName, value as port FROM #GetPort

DROP TABLE #GetPort





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #683784
Posted Thursday, March 26, 2009 11:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:20 PM
Points: 194, Visits: 334
Thank you both for the idea and scrpit. I am working on this..
I understand that the script given will work if I am connected to server only. However the issue is I can not connect to the servers. So I need to run this (similer) code against all servers (name will be found in a table) using some batch
If you have furher idea.. plz share
Thanks again



CDBA
Post #684442
Posted Thursday, March 26, 2009 12:08 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 12,963, Visits: 32,512
i tried running the command over a linked server; basically i tried changing the last command to a 4 part command:
EXEC linkedservername.master..xp_regread 'HKEY_LOCAL_MACHINE', @inst, @inst1

got this error:

SOFTWARE\Microsoft\Microsoft SQL Server\SQLEXPRESS\MSSQLServer\SuperSocketNetLib\Tcp\\\\////TcpPort
Msg 7411, Level 16, State 1, Line 43
Server 'db1' is not configured for RPC.

maybe if RPC was enabled for the server, but i dunno...it's getting out of my area of knwoledge.

i think you'll have to do this in a programming language, or use RegEdit to connect to the remote machines using your domain credentials manually.

you could also try SQLRecon.exe, which scans a network for servers and reports back a lot of information, but it is not possible via a linked server.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #684453
Posted Thursday, March 26, 2009 7:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:20 PM
Points: 194, Visits: 334
Thanks a lot... I will try to explore from my end also and will post here if I get a better way..
Thanks again



CDBA
Post #684733
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse