views:

294

answers:

1

Currently have this to get a value from the registry in TSQL. However, I need to get the DigitalProductId and it does not return the value required. I think its stored as a binary in the registry.

Any ideas?

DECLARE @retvalue int, @data varchar(500)
EXECUTE @retvalue = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
'DigitalProductId', @param = @data OUTPUT
PRINT 'ProductId: '+ @data

Edit: I have updated the question and the code.

A: 

DigitalProductId is a REG_BINARY key.

xp_instance_regread casts everything it reads into a null-terminated string, so this won't ever return anything meaningful if the string contains NULLs.

xp_instance_regread is an undocumented procedure that is not intended to be used in production environment. If you need to read the registry keys from SQL Server's side, you better write your own XP to do it.

Quassnoi
So, it is possible to CAST back to a displayable value? The DigitalProductId is like this PWK9P-6GFTM-792RH-JDM92-TXV9E when properly extracted from the registry.
Coolcoder
It needs to work in all versions of SQL (2000, 2005 , 2008) including express editions. This proc is built in and I didnt think it was possible to do any other way unless a CLR proc is developed which would require 2005 minimum and clr enabled as a pre-req.
Coolcoder
`@Coolcoder`: The `XP` just returns a pointer to the data, which `SQL Server` casts into a `VARCHAR` (since that's what the `XP` expects). The data contains double zeros which are treated as the string separators and the string is truncated on them. So when the registry value reaches the `SQL Server`, everything after the first zero will be irrecoverably lost.
Quassnoi
@Quassnoi So it isnt possible without resulting to a CLR proc?
Coolcoder
`@Coolcoder`: this is not a `CLR`, this is an extended stored procedure. This works from `2000` onwards (and probably, even earlier)
Quassnoi
@Quassnoi Sorry, I know the current proc is not a CLR proc. What I mean is, it sounds like the current XP cannot output what I require so the question is, is it possible to get what I need without resulting to using a CLR?
Coolcoder