Solved

ADO Recordset shows BOF and EOF true / Query Analyser does not agreee

Posted on 2003-12-03
20
608 Views
Last Modified: 2012-05-04
System:
SQL Server 2000
ADO 2.6 from ASP
Using Provider=SQLOLEDB
MultipleRecorsets

Issue:
From an ASP we run a SP that returns multiple RSs, using NextRecordset to move from one to the next. The final RS looks to be empty to ADO - the BOF and EOF properties are true. However, the same SP, when run through Query Analyser, shows what we know to be true - the final RS DOES contain records.

What can be going on here?
How is the driver getting spooked (if that's the case)?
Not that it should matter, but the final RS is being pulled from a temporary table.
0
Comment
Question by:RhinoInformation
  • 7
  • 4
  • 3
  • +3
20 Comments
 
LVL 19

Expert Comment

by:Dexstar
ID: 9868632
@RhinoInformation:

> From an ASP we run a SP that returns multiple RSs, using NextRecordset to move
> from one to the next. The final RS looks to be empty to ADO - the BOF and EOF
> properties are true. However, the same SP, when run through Query Analyser,
> shows what we know to be true - the final RS DOES contain records.

When you run it from the Query Analyzer, how many messages do you get about the number of rows affected?  2?

Hope That Helps,
Dex*
0
 
LVL 6

Accepted Solution

by:
lausz earned 100 total points
ID: 9868817
TRY ADDING THIS LINE TO YOUR CODE ..

SET NOCOUNT ON
0
 
LVL 6

Assisted Solution

by:lausz
lausz earned 100 total points
ID: 9869084
...at the beginning
0
 

Author Comment

by:RhinoInformation
ID: 9869194
(oops *Analyser = Analyzer)

lausz
We had already placed "SET NOCOUNT ON" at the top of the SP. This was something we discovered long ago that "spooked" the driver. I think you're on the right track since this seems to be very similar behavior.

Dexstar
Since we had set the line above, we don't see the # of records affected. What do you suspect? Should I comment that line for debugging purposes? (Though ADO will not see the following 2-n recordsets then.)

Thanks...
0
 
LVL 19

Assisted Solution

by:Dexstar
Dexstar earned 100 total points
ID: 9869263
Well, you must be doing something different from what I usually do.  I've found stored procedures that return recordsets don't work properly unless I use "SET NOCOUNT OFF"!, but you've found the opposite, so I'm not sure.

Try different combinations of "SET NOCOUNT ON" and "SET NOCOUNT OFF" both before you select the first recordset, and before you select the 2nd one.

Dex*
0
 

Author Comment

by:RhinoInformation
ID: 9869449
Dex*
My understanding is that setting NOCOUNT ON suppresses the messages returned from the DB (at least the count messages). What I had read was that, using OLEDB, these messages throw off the RS object (or something to that effect). Under ODBC we didn't see this behavior. It has been working great for months. If I remove that line I'll get the counts in QA but no RSs through ADO.

Still trying things, but would love to get the easy answer...
0
 
LVL 6

Expert Comment

by:lausz
ID: 9869470
Stupid question...but ..
Are you sure your connection string is ok ?

0
 

Author Comment

by:RhinoInformation
ID: 9869629
Yeah, because the entire site is data driven and everything else returns fine. This thing processes about 20 recordsets in all. Some pages even more. But this one function that creates an HTML string returns about 9 RS and only the last one doesn't show up - I've put in a ton of debug code and it's all because BOF and EOF are true - though they shouldn't be.

I've alse dumped out the RS.State and it shows the RS is complete (not still processing or anything).
0
 

Author Comment

by:RhinoInformation
ID: 9869971
This may help...I have learned there is one difference between the last RS and all that precede it. The last recordset is created using dynamic SQL - so while the others are hard coded within the SP (i.e., "SELECT LastName, FirstName FROM ...") the last one is dynamic (i.e., "EXEC @SQLSelectCode").

This is obviously making a difference, but we're not sure why...
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9869972
I had the same problem and it in my code i had a

"PurchaseNumber = " & cstr(PNum)

Purchase Number is an INT

In Query Analyzer it worked in ADO it didnt

so i changed

"PurchaseNumber = " & clng(PNum)

And it worked. So probably you have a type mismatch
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:RhinoInformation
ID: 9870272
Mikal613
All the code is executed in the SP. ADO / QA simply gets the results. Just the same, I checked for datatype issues. There are none.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9873446
Have you tried moving your last SQL statement to the start or the middle of the SP, just to work out wether the issue is due to the SQL statement or the position of it?

I would say that the dynamic SQL is causing the problem.

Perhaps you can return the dynamic SQL string that is created as a SP parameter to the application and print it, just to see exactly how SQL Server has decided to build the dynamic SQL.
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9874443
You misunderstood The Datatype of the actual String Query from ASP Must be the SAme datatype as to the SQL.
0
 
LVL 7

Expert Comment

by:pegasys
ID: 9879729
Can you check the tSQL is generated correctly..

Just before you run your query in the .ASP try this:

StrSQL="select... wahtever..."
response.write(StrSQL)
response.end

Just before you fire the statement

Compare that to the SQL statement that you are running... That should fix your problem.. If not, run that code that IExplore pops up with in Query Ana. and that should give you a zero resultset too...

Recheck your code :-)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9880931
To clarify for everyone, ... the dynamic sql is generated in the Stored Procedure, not in the ASP. (I think-correct me if I'm wrong)
0
 
LVL 48

Assisted Solution

by:Mikal613
Mikal613 earned 100 total points
ID: 9882750
are you using keywords in the query if so put brackets [  ]
0
 

Author Comment

by:RhinoInformation
ID: 9896991
nmcdermaid is correct. The dynamic SQL is generated in the SP, which means the same code is executed for Query Analyzer and ADO through the ASP.

We have tried hacking this thing up and trying all sorts of variations. The strange thing is that ADO sees that there is one more RS (the final one created by EXEC() in the SP) but it believes that it's an empty RS. QA, however, sees all the records that are really there.

Increaing points again.....
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 100 total points
ID: 9897311
perhaps go one step further and insert the contents of your dynamic SQL variable into a table, then select out of that table and cop and run the text, I really think it's the dynamic SQL.

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 9897322
.. and/or move the dynamic SQL away from the end to verify wether it is the SQL statement or its position in the recordsets.
0
 

Author Comment

by:RhinoInformation
ID: 9966986
THE ANSWER:

I gave the most persisten people the points. Thanks for your help. I was lead to believe both queries were being executed in the exact same way - and that was almost true. A key variable being passed into the SP for the final RS was not being passed in properly from the ASP - so the final RS failed.

Also, there was another message being generated by the server that was throwing off ADO ...

Thanks again -
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now