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

XP_INSTANCE_REGREAD Expand / Collapse
Author
Message
Posted Thursday, May 18, 2006 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2007 5:07 AM
Points: 6, Visits: 1

I have a SQL2005 question:

 

I have a query that runs fine on SQL2000

declare @rc int,
@dir nvarchar(5)

exec @rc = master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp',N'TcpPort', @dir output

select @dir

@dir returns 1433

Under SQL2005 it returns

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

Anyone have an idea?

thanks

 

 




Mike Demmitt
Post #281045
Posted Thursday, May 18, 2006 10:02 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, May 7, 2012 10:05 AM
Points: 165, Visits: 134
Have you checked to verify the key exists?  That error indicates a key (a.k.a. file) is not present.
Post #281143
Posted Wednesday, October 25, 2006 4:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 11, 2014 5:56 PM
Points: 3, Visits: 16

yes, it is folder heirarchy is changed in SQL 2005.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll\TcpPort

It contains one folder with spaces (Microsoft SQL Server) and now master.dbo.xp_instance_regread is giving error there. I tried to put single quote, double quote, all types of braces but none of them worked.

Anybody knows how to resolve this?

Post #318138
Posted Thursday, January 11, 2007 3:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:41 PM
Points: 219, Visits: 280

Try using xp_regread, such as:

declare @rc int,

@dir varchar(5)

exec @rc = master.dbo.xp_regread

'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\Tcp\IPAll','TcpPort', @dir output

select @dir

 

Tim




Post #336299
Posted Friday, June 12, 2009 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 12:30 PM
Points: 1, Visits: 19
Apparently, you can use xp_instance_regread with keys that have spaces if you double up the backslash on the element that has spaces, e.g.:

exec master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\\Microsoft SQL Server\FXOTDBSQL\MSSQLServer\SuperSocketNetLib\Tcp', 'TcpPort', @port OUTPUT

Notice the doubled-up backslash before "Microsoft SQL Server".
Post #734054
Posted Thursday, March 3, 2011 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 22, 2011 3:29 AM
Points: 4, Visits: 35
Great catch on doubling the '\'.
Thanks!
Post #1072616
Posted Friday, August 26, 2011 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 15, 2014 6:53 AM
Points: 1, Visits: 664
I'm not sure that it works in quite that way, I think the double \\ is more likely telling regread and regwrite not to perform its substitution for the current SQL version and instance name.

The simplicity of these extended stored procedures is that you can use the generic key root and let SQL work out exactly where the registry key is located, then your code becomes portable.

So
“Software\Microsoft\MSSQLServer\MSSQLServer”
becomes (in my 2008 R2 default instance)
“Software\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer”
Post #1166160
Posted Wednesday, February 1, 2012 12:58 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:38 PM
Points: 393, Visits: 138
This is the answer I was looking for, Thanks!
Post #1245298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse