Solved

SQL Server - ADODB.Recordset error '800a0e78' - OLEDB question

Posted on 2004-09-03
12
10,172 Views
Last Modified: 2012-05-05
Hello, everyone,

I've been beating my head against the keyboard for too many hours on this one.  I have an (incredibly ugly, inherited) ASP application running with a SQL Server backend.  I initially had a live version of the ASP app and MSSQL db running on a dedicated server, and a test version running on a separate shared hosting server, both using ODBC DSN connections.

I recently moved the test db back to the same box as the live server (not ideal, I know, but there are a variety of constraints that drove me to this action), and implemented an OLEDB connection.  Everything seems to work on the site except for one stored-procedure call.  Both are running on a SQL Server 7 backend.

Here is the code snippet:

Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = conSM
objRS.Open sSQLwti, conSM
objRS.MoveFirst

conSM is defined in a global.asa file.  sSQLwti generates a stored procedure call with parameters that, when run in Query Analyzer, returns a valid dataset in the Grid.  The stored procedure DOES include a SET NOCOUNT ON statement at the top (right after the AS line).  The counts appear to be suppressed in Query Analyzer, but a number of print statement comments do appear in the messages panel.

The objRS.MoveFirst generates the error: ADODB.Recordset error '800a0e78'.  Operation is not allowed when the object is closed.

I have tried a Debug objRS.State and it returns 0 when run with the stored procedure call.  I have tried substituting simple SQL for sSQLwti and it seems to work fine, so I do not believe it is a connection problem.  It appears that somehow the result set is not being picked up by the ASP page.  Any ideas?  Any help would be mucho appreciated!!!!

Thanks,

LL

0
Comment
Question by:nhtechgal
12 Comments
 

Author Comment

by:nhtechgal
ID: 11977719
P.S. I've tried replacing the first three lines with the following, with the same results:

cmdSearch.CommandText = sSQLwti
Set objRS = cmdSearch.Execute(, , adCmdText)
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 11977784
PRB: Recordset DTC Error with Empty Recordset
http://support.microsoft.com/default.aspx?scid=kb;en-us;301437
0
 

Author Comment

by:nhtechgal
ID: 11977817
I've seen that article, but I didn't think it applied.  It says, for example, that the solution listed "allows a SQL statement that may return an empty recordset to be executed."  I don't want a SQL statement that returns an empty recordset to be executed.  I want a SQL statement that returns a valid dataset in Query Analyzer to return a matching, non-empty recordset in ASP.  Did I misinterpret the MSKB article?
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 11977854
I think all it is pointing out is that the error you're getting is due to a an empty recordset being returned, whether you think you should get one or not.  To me, this error is more a symptom of something else that is happening before that where you aren't getting something in your recordset.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11983093
Agree with carlwarner--you should always check for EOF or BOF on a recordset before you attempt to perform any operation against it....

Did you try and run the SQL statement that sSQLwti contains within query analyzer to make sure it was a good SQL statement and that it returns rows?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 51

Expert Comment

by:Julian Hansen
ID: 11984108
The code seems fine and the comments from CarlWarner and arbert are also very valid.

Just to be 100% sure it is not your connection object paste the following code before the first line in your sample above

   If conSM.State = adStateOpen Then
      Repsonse.Write "Connection is open"
      ...
      Put the rest of your recordset creation code here.
   Else
      Response.Write "Connection is not ready"
   End If
0
 
LVL 1

Expert Comment

by:litomd
ID: 11985924
Also try to replace

Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = conSM
objRS.Open sSQLwti, conSM
objRS.MoveFirst

With this

Set objRS = conSM.Execute(sSQLwti)
objRS.MoveFirst

On the other hand... a MoveFirst operation is not needed anyway... what I usually do is:

Set objRS = conSM.Execute(sSQLwti)
Do Until objRS.EOF
  ...
  operations go here
  ...
  objRS.MoveNext
Loop

Thus avoiding a check for empty recordset...

Last, Set objRS = Server.CreateObject("ADODB.Recordset") returns a closed recordset... and even in this question you used the same objRS variable, you may be using a slightly different one that can be misspelled... I know it is a hassle but try to rewrite your code using Option Explicit in the first line to avoid using different variables without notice. You will have to DIM all variables.

Hope this helps.
0
 

Author Comment

by:nhtechgal
ID: 11993701
Thanks everyone for your comments.  To make a long story short, I had a strong hunch as to what the problem was, and ultimately finally found a reference to the situation.  It was indeed, as I suspected, an OLEDB-related issue.  In addition to requiring a SET NOCOUNT ON statement, I had to comment out all of my interim print statements, and voila, it worked!

http://www.mail-archive.com/proasp_howto@p2p.wrox.com/msg06019.html

In the meantime, I’d already written the following response (twice, since the first time I submitted it, EE ate it!), so I’m going to go ahead and post it.  Hope that’s kosher.

Thanks again,

LL

--------------------------------------------------------------------------------------------------

Comment from arbert:
Agree with carlwarner--you should always check for EOF or BOF on a recordset before you attempt to perform any operation against it....Did you try and run the SQL statement that sSQLwti contains within query analyzer to make sure it was a good SQL statement and that it returns rows?

Response:
I’ve tried the BOF/EOF suggestion and it produces the same error message.  Again, I believe this is because it is not returning an empty recordset, but rather, no recordset.  As for using Query Analyzer, as noted in the original post, the SQL works fine there.  Specifically, I have put in a Debug sSQLwti line and then copied and pasted the resulting SQL into Query Analyzer.  It returns the expected four records.  When I test it with Set NoCount Off displays the expected interim checks.  And yes, I have checked that I did indeed turn NoCount back On.

Comment from julianH:
Re: testing the connection state.

Response:
I’d already done this.  If I insert the line in the existing code, it indicates the Connection state is closed.  However, if I either run a simple SQL statement (e.g., Select * from a table), it returns a recordset.  I have also done some Debugs on the connection itself, and it is indeed recognizing the correct Active Connection (set in Global.asa).

Comment from litomd:
Try to replace [existing code] with… Set objRS = conSM.Execute(sSQLwti)…Use a Do Until EOF loop instead, and you may be using a slightly different one that can be misspelled... I know it is a hassle but try to rewrite your code using Option Explicit in the first line to avoid using different variables without notice. You will have to DIM all variables.

Response:
I tried the suggested code, but still get the same error message.  I agree with the check Do Loop – in general that makes sense, but I just haven’t gotten around to retrofitting all of the (800-plus pages of) code I inherited (and my client hasn’t budgeted for it).  As for the variables, Option Explicit is a good suggestion – at least all the variables are Dim’ed.  

But I am 99.99% positive that is not the situation in this case.

GENERAL SUMMARY:

Thanks for all the suggestions and for some good reminders, but I don’t think any of these are on the right path – I am pretty sure this change was generated by a server change (and specifically to an OLEDB connection).

Remember, the exact same code functioned in the following situations:

•      On a SQL Server 7 database with a local ODBC connection to the live site
•      On a SQL Server 2000 database with a local ODBC connection to the test site

And the only difference is now that I am running it off a remote test site via an OLEDB link to a SQL Server 7 database.  In fact, when I started to question myself, I resynched both (the fulling functioning) live site and database to the test web and database server (with the exception of the Global.ASA file) and still got the above error.  Incidentally, all of the other SQL/stored procedure calls tested so far appear to be functioning on the test site.

I cannot create a DSN on the remote test server without changing web hosts (not a preferred option) since that server serves multiple functions.

Is it possible that in addition to requiring a Set NoCount On statement that I need to comment out the print statements?  I have seen many references to NoCount and OLEDB, some including print statements, so it seems like it should be OK.

And again, when I run the query in QA, I get the same (apparent) results in the live and test databases.

Any other suggestions would still be greatly appreciated.

Thanks!

LL
0
 
LVL 33

Expert Comment

by:CarlWarner
ID: 11999541
Sure, we don't mind using our time to debug the problem.
0
 

Expert Comment

by:geras
ID: 12024726
I think the problem is that you didn´t open your connection at first time.
Try this.
'Open your connection first
With conSM
            cnn.ConnectionString = "Provider=SQLOLEDB;" & _
                                "Data Source=SERVER;" & _
                                "Initial Catalog=DATABASE;" & _
                                "User ID=USERNAME;" & _
                                "Password=PASSWORD;"
            .IsolationLevel = adXactIsolated
            .Mode = adModeShareExclusive
            .ConnectionTimeout = 5
            .Open
            .Execute "SET LOCK_TIMEOUT 0"
End With
'Then your code
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS.ActiveConnection = conSM
objRS.Open sSQLwti, conSM
objRS.MoveFirst
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 12034256
Closed, 500 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Read about achieving the basic levels of HRIS security in the workplace.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

17 Experts available now in Live!

Get 1:1 Help Now