XP_INSTANCE_REGREAD

Question

Post reply

XP_INSTANCE_REGREAD

mdemmitt

SSC Veteran

Points: 248
More actions
May 18, 2006 at 7:21 am

#117716

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
May 21, 2022 at 7:01 am

This was removed by the editor as SPAM
May 25, 2022 at 5:16 am

This was removed by the editor as SPAM
March 1, 2023 at 1:13 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

R2ro Default port Points: 1419 More actions · Answer 1

Have you checked to verify the key exists? That error indicates a key (a.k.a. file) is not present.

Sandesh Moghe Valued Member Points: 73 More actions · Answer 2

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?

tknoobOLD Ten Centuries Points: 1183 More actions · Answer 3

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

Yoshi Henderson Valued Member Points: 67 More actions · Answer 4

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".

Jon Guetlein Valued Member Points: 66 More actions · Answer 5

Jon Guetlein

Valued Member

Points: 66

March 3, 2011 at 8:55 am

#1294069

Great catch on doubling the '\'.

Thanks!

Barry.Cussens Valued Member Points: 73 More actions · Answer 6

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”