Help with Reading SQL Output Parameter

[sonname]

I want to execute an sql statetement in my program and get the results from the output variable. I'll be using ADODB, but I'm not sure how to get the output returned from the following. Can anyone help?

DECLARE @data_dir varchar(500)
EXECUTE master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLDataRoot', @param = @data_dir OUTPUT

I need to get the value in @data_dir. Does anyone have some code example? I don't know the names of the input paramater for the xp_instance_regread stored procedure as this is an undocumented microsoft stored procedure.

Thanks

 

[gmmastros]

You were very close. You don't need @param. Also, @data_dir will be set to the value in the registry. To return it to the client (your VB app) you just need to select it, like this...

[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

[COLOR=blue]DECLARE[/color]  @data_dir [COLOR=blue]varchar[/color](500)

[COLOR=blue]EXECUTE[/color] master.dbo.xp_instance_regread [COLOR=red]'HKEY_LOCAL_MACHINE'[/color],
[COLOR=red]'SOFTWARE'[/color],
[COLOR=red]'SQLDataRoot'[/color], @data_dir [COLOR=blue]OUTPUT[/color]

[COLOR=blue]Select[/color] @data_dir [COLOR=blue]As[/color] DataDirectory

This query will return a recordset with 1 row and 1 column. The column name will be DataDirectory. Copy/paste this to Query Analyzer and run in. Once you do, I think you will have an easy time calling this from VB.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Alternatively, you could simplify this a little. You don't need to call xp_instance_regread with the output parameter (it's optional). So, your code could be...

[COLOR=blue]EXECUTE[/color] master.dbo.xp_instance_regread [COLOR=red]'HKEY_LOCAL_MACHINE'[/color],
[COLOR=red]'SOFTWARE'[/color],
[COLOR=red]'SQLDataRoot'[/color]

When I run this on my computer, I get....

[tt][blue]
Value Data
------------ -------------------------------------------
SQLDataRoot C:\Program Files\Microsoft SQL Server\MSSQL
[/blue][/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom