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