One Sql Command, Two Result Sets

[Michael C#]

Hi all,

I'm sending a command via SqlClient, and it returns two result sets. I can
successfully read the first result set, but how can I access the second
result set? Here's an example of my SqlCommand CommandText:

DECLARE @result AS INT\nEXEC @result = master..xp_cmdshell 'dir "c:\*.*" /b
/a-d'\nSELECT @result

When run on SQL Server, as

DECLARE @result as int
EXEC @result = master..xp_cmdshell 'dir "c:\klh\*.*" /b /a-d'
SELECT @result

It returns 2 separate result sets, i.e.:

Result set 1
-------------------
AUTOEXEC.BAT
boot.ini
CONFIG.SYS
IO.SYS
MSDOS.SYS
NTDETECT.COM
ntldr
pagefile.sys
Thumbs.db

Result set 2
---------------------
0

In this instance, a return code of 0 indicates success, 1 indicates failure
of the xp_cmdshell command. How can I access the return code? Maybe I'm
going about it wrong. Thanks.

 

[Michael C#]

P.S. - Another example is running the sp_helpdb 'databasename' command.
This returns two result sets also. If anyone knows the best way to access
the second result set as well as the first, it would be much appreciated.

Thanks

 

[Michael C#]

Never mind, got the answer. Thank you.

 

[Richard Blewett [DevelopMentor]]

Use the NextResult() method on the data reader you get back from ExecuteReader. This advances to the next result set.

Regards

Richard Blewett - DevelopMentor
http://www.dotnetconsult.co.uk/weblog
http://www.dotnetconsult.co.uk

Hi all,

I'm sending a command via SqlClient, and it returns two result sets. I can
successfully read the first result set, but how can I access the second
result set? Here's an example of my SqlCommand CommandText:

DECLARE @result AS INT\nEXEC @result = master..xp_cmdshell 'dir "c:\*.*" /b
/a-d'\nSELECT @result

When run on SQL Server, as

DECLARE @result as int
EXEC @result = master..xp_cmdshell 'dir "c:\klh\*.*" /b /a-d'
SELECT @result

It returns 2 separate result sets, i.e.:

Result set 1
-------------------
AUTOEXEC.BAT
boot.ini
CONFIG.SYS
IO.SYS
MSDOS.SYS
NTDETECT.COM
ntldr
pagefile.sys
Thumbs.db

Result set 2
---------------------
0

In this instance, a return code of 0 indicates success, 1 indicates failure
of the xp_cmdshell command. How can I access the return code? Maybe I'm
going about it wrong. Thanks.