SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XP_INSTANCE_REGREAD


XP_INSTANCE_REGREAD

Author
Message
mdemmitt
mdemmitt
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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
R2ro
R2ro
Old Hand
Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)Old Hand (381 reputation)

Group: General Forum Members
Points: 381 Visits: 134
Have you checked to verify the key exists? That error indicates a key (a.k.a. file) is not present.
Sandesh Moghe
Sandesh Moghe
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 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?


tknoob
tknoob
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 325

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
Yoshi Henderson
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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".
Jon Guetlein
Jon Guetlein
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 35
Great catch on doubling the '\'.
Thanks!
Barry.Cussens
Barry.Cussens
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 706
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”
astebler
astebler
SSC-Addicted
SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)SSC-Addicted (496 reputation)

Group: General Forum Members
Points: 496 Visits: 175
This is the answer I was looking for, Thanks!
GP Van Eron
GP Van Eron
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3564 Visits: 472
Thanks for the \\ tip. Huge help.
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