How to get DigitalProductId from Registry via TSQL

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 ques开发者_JAVA技巧tion and the code.

---

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.

---

you can get around the string truncation as follows:

declare @DigitalProductID varbinary(16);
declare @xp_instance_regread table ([key] nvarchar(128), [value] varbinary(16));
insert into @xp_instance_regread
exec master..xp_instance_regread N'HKEY_LOCAL_Machine',N'SOFTWARE\Microsoft\MSSQLServer\Setup',N'DigitalProductID'
select @DigitalProductID = [value] from @xp_instance_regread
select 'DigitalProductID',@DigitalProductID