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

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)

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

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?

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.

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?

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

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.

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

Sure, we don't mind using our time to debug the problem.

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