Toughie: multiple ADO recordsets fail using a SP

Toughie: multiple ADO recordsets fail using a SP

Post by Lou Houlemard » Wed, 01 Sep 1999 04:00:00



Using SQL 7.0,  ADO 2.1 from VB

Guys, this is driving me bonkers. I can use ADO to access multiple
recordsets from SP. Works like a charm UNLESS I put anything other than
SELECT statements in my SP. One INSERT statement kills the whole recordset
group - it will not open. rs.NextRecordset can't even be used.

I suspect SQLOLEDB or ADO doesn't know what to do when SQL returns the
results from such a query like:

"2751 row(s) affected."

Is there any way to tell SQL server or the SP not to tell the driver
anything  -- or is there something in ADO I'm missing that I need to
handle -- parameter-wise maybe?? I'm not trying to return any values, just
the multiple SELECT result sets.

Really at wit's end here.

 
 
 

Toughie: multiple ADO recordsets fail using a SP

Post by Kurt Mila » Wed, 01 Sep 1999 04:00:00


I believe this may be what you're looking for:

SET NOCOUNT ON
'Run your INSERTs in here
SET NOCOUNT OFF
'Run your SELECTs out here

I use this method to INSERT a new record into a table and SELECT the

kills the "rows affected" messages and such, and that NOCOUNT OFF turns them
back on.

HTH

Kurt



Quote:> Using SQL 7.0,  ADO 2.1 from VB

> Guys, this is driving me bonkers. I can use ADO to access multiple
> recordsets from SP. Works like a charm UNLESS I put anything other than
> SELECT statements in my SP. One INSERT statement kills the whole recordset
> group - it will not open. rs.NextRecordset can't even be used.

> I suspect SQLOLEDB or ADO doesn't know what to do when SQL returns the
> results from such a query like:

> "2751 row(s) affected."

> Is there any way to tell SQL server or the SP not to tell the driver
> anything  -- or is there something in ADO I'm missing that I need to
> handle -- parameter-wise maybe?? I'm not trying to return any values, just
> the multiple SELECT result sets.

> Really at wit's end here.


 
 
 

Toughie: multiple ADO recordsets fail using a SP

Post by Tibor Karasz » Thu, 02 Sep 1999 04:00:00


Lou,

That is interesting. I knew that ADO might *on "rows affected" (DONE_IN
_PROC) messages, but I didn't know that there is a difference if the
DONE_IN_PROC is returned by a SELECT or by UPD/INS/DEL. Makes sense when you
think about it. UPD doesn't return column meta-data, so ADO doesn't
understand a row-count without column-metadata. (Hey ADO developers at MS,
do you read this? :-).

As Kurt suggested, just SET NOCOUNT ON at the beginning of your proc code
(or the batch, if it isn't a proc). AFAIK, you don't have to turn it on
before a SELECT (I'm fairly certain of that).

There's a trace flag to disable DONE_IN_PROC globally, at the server level.
Search for "trace flag".

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.



Quote:> Using SQL 7.0,  ADO 2.1 from VB

> Guys, this is driving me bonkers. I can use ADO to access multiple
> recordsets from SP. Works like a charm UNLESS I put anything other than
> SELECT statements in my SP. One INSERT statement kills the whole recordset
> group - it will not open. rs.NextRecordset can't even be used.

> I suspect SQLOLEDB or ADO doesn't know what to do when SQL returns the
> results from such a query like:

> "2751 row(s) affected."

> Is there any way to tell SQL server or the SP not to tell the driver
> anything  -- or is there something in ADO I'm missing that I need to
> handle -- parameter-wise maybe?? I'm not trying to return any values, just
> the multiple SELECT result sets.

> Really at wit's end here.

 
 
 

Toughie: multiple ADO recordsets fail using a SP

Post by Charles Bretana Jr » Thu, 02 Sep 1999 04:00:00


Tibor,
    I also use Set NoCount On at the beginning of all my Procs, and do not
ever turn it off again......  Have had no propblems using that technique

Charly


> Lou,

> That is interesting. I knew that ADO might *on "rows affected"
(DONE_IN
> _PROC) messages, but I didn't know that there is a difference if the
> DONE_IN_PROC is returned by a SELECT or by UPD/INS/DEL. Makes sense when
you
> think about it. UPD doesn't return column meta-data, so ADO doesn't
> understand a row-count without column-metadata. (Hey ADO developers at MS,
> do you read this? :-).

> As Kurt suggested, just SET NOCOUNT ON at the beginning of your proc code
> (or the batch, if it isn't a proc). AFAIK, you don't have to turn it on
> before a SELECT (I'm fairly certain of that).

> There's a trace flag to disable DONE_IN_PROC globally, at the server
level.
> Search for "trace flag".

> --
> Tibor Karaszi
> MCDBA, MCSE, MCSD, MCT, SQL Server MVP
> Cornerstone Sweden AB
> Please reply to the newsgroup only, not by email.



> > Using SQL 7.0,  ADO 2.1 from VB

> > Guys, this is driving me bonkers. I can use ADO to access multiple
> > recordsets from SP. Works like a charm UNLESS I put anything other than
> > SELECT statements in my SP. One INSERT statement kills the whole
recordset
> > group - it will not open. rs.NextRecordset can't even be used.

> > I suspect SQLOLEDB or ADO doesn't know what to do when SQL returns the
> > results from such a query like:

> > "2751 row(s) affected."

> > Is there any way to tell SQL server or the SP not to tell the driver
> > anything  -- or is there something in ADO I'm missing that I need to
> > handle -- parameter-wise maybe?? I'm not trying to return any values,
just
> > the multiple SELECT result sets.

> > Really at wit's end here.

 
 
 

Toughie: multiple ADO recordsets fail using a SP

Post by Lou Houlemard » Thu, 02 Sep 1999 04:00:00


this does the job.
 
 
 

1. DB2 SP/ADO/Multiple recordsets

We have an OS/390 COBOL stored procedure that returns 2 resultsets.
Works great via a desktop VB app.  Calling via ADO in an ASP page, we
cannot get the second recordset.  Checking the error after issuing the
rs = rs.NextRecordset results in:
3251
Current provider does not support returning multiple recordsets from a
single execution

At one time this was working for us. The server is IIS 5 and MDAC is
2.7 SP 1.

The ADO cursorlocation is adUseClient.  The 1st recordset comes over
just fine.  Anyone have a similar problem?

thanks,

Phil Jackson

2. Visual Basic and FoxPro

3. Multiple SPs OR one SP w/ Multiple Recordsets (Part 2)

4. MSDN

5. which language for stored procs ?

6. Batch update for multiple tables using single ADO Recordset (VB )

7. using a variable_name as a column name in the set clause of the update statement

8. Using ADO Recordsets and ODBC fails to insert records with Unicode characters

9. Multiple Recordsets Using VB, ADO and SQL 7.0 Stored Procedures

10. ADO - Using multiple recordsets

11. Batch update for multiple tables using single ADO Recordset (VB )

12. ADO fails to release Recordset cursor after opening disconnected Recordset