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


XP_INSTANCE_REGREAD


XP_INSTANCE_REGREAD

Author
Message
mdemmitt
mdemmitt
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

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

Group: General Forum Members
Points: 761 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
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: 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
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 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
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 35
Great catch on doubling the '\'.
Thanks!
Barry.Cussens
Barry.Cussens
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 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
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 186
This is the answer I was looking for, Thanks!
GP Van Eron
GP Van Eron
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4006 Visits: 477
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