473,326 Members | 2,815 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

One Sql Command, Two Result Sets

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.
Nov 16 '05 #1
3 3812
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#" <xy*@abcdef.com> wrote in message
news:8s***************@fe10.lga...
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.

Nov 16 '05 #2
Never mind, got the answer. Thank you.
"Michael C#" <xy*@abcdef.com> wrote in message
news:8s***************@fe10.lga...
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.

Nov 16 '05 #3
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.
Nov 16 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: X | last post by:
I have a varchar field which contains a comma delimeted set of data. I am trying to us the ELT function to extract the first part of the group. Problem is, ELT does not seem to work when the data...
5
by: Stanley Sinclair | last post by:
I have a need to return multiple result sets from a stored procedure. Want that SP to call others to get the data. Win2003, db2 8.1.5. Can't figure out how to handle open cursors, and return...
1
by: Arijit Chatterjee | last post by:
Dear Faculties, I have a query on this statement.. =============================== CREATE PROCEDURE Check_Manage ( ) DYNAMIC RESULT SETS 1 ============================== I want to know the...
15
by: AussieRules | last post by:
Hi, I have a need to do two selects against to stored proc's in my SQL db. At the moment, each SP is called and two different dataset are populate. Thats two round trips to the SQL server. ...
8
by: Ivan | last post by:
Hi I am new in DB2, and I have some problems when I try run stored procedures, and others statements. I made one stored procedure very simple, but this show different messages. I have followed...
12
by: robertino | last post by:
Hi all, I've put together a few SPs to produce a BOM (bill of materials) listing, which together use a couple of global temp tables, and return the results from a cursor. Here's the code: ...
10
by: Bob | last post by:
Hi, i wrote code for inserting data into a table, but it runs twice. If i remove the line: "comd.ExecuteNonQuery()", then it runs once; but i thought that line was necessary for executing the...
6
by: happyhondje | last post by:
Hello everyone, I've got a little issue, both programming and performance-wise. I have a set, containing objects that refer to other sets. For example, in a simple notation: (<a, b, c>, <d, e>)...
2
by: Yash | last post by:
Hi, We are in the process of tuning the performance of our stored procs in SQL 2000 and are looking for a tool that would help us in comparing the result sets of an old SP and a modified SP. The...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.