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

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.
RhinoInformationAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DexstarCommented:
@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
lauszCommented:
TRY ADDING THIS LINE TO YOUR CODE ..

SET NOCOUNT ON
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lauszCommented:
...at the beginning
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RhinoInformationAuthor Commented:
(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
DexstarCommented:
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
RhinoInformationAuthor Commented:
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
lauszCommented:
Stupid question...but ..
Are you sure your connection string is ok ?

0
RhinoInformationAuthor Commented:
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
RhinoInformationAuthor Commented:
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
Mikal613Commented:
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
RhinoInformationAuthor Commented:
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
nmcdermaidCommented:
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
Mikal613Commented:
You misunderstood The Datatype of the actual String Query from ASP Must be the SAme datatype as to the SQL.
0
pegasysCommented:
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
nmcdermaidCommented:
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
Mikal613Commented:
are you using keywords in the query if so put brackets [  ]
0
RhinoInformationAuthor Commented:
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
nmcdermaidCommented:
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
nmcdermaidCommented:
.. 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
RhinoInformationAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.