?
Solved

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

Posted on 2003-12-03
20
Medium Priority
?
621 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 300 total points
ID: 9868817
TRY ADDING THIS LINE TO YOUR CODE ..

SET NOCOUNT ON
0
 
LVL 6

Assisted Solution

by:lausz
lausz earned 300 total points
ID: 9869084
...at the beginning
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 300 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
 

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 300 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 300 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 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