SQLOLEDB, Stored Procedure returning record Set

[JR4VB]

I have a stored procedure that Runs several queries creating a temp table. I then select from that temp table returning those rows into my RecordSet in a vbscript ASP.
It works just fine using the follow connection string:

Conn.ConnectionString = "driver={SQL Server};Server=Server;Database=Test;uid=Me;pwd=pass"
Conn.Open
Rs.Open SQL, Conn

When I switched to using SQLOLEDB I got the following error:
ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

Here are the things I have tried:

Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=Server;" & _
"Initial Catalog=Test;User Id=Me;Password=pass;" & _
"Network Library=dbmssocn;"

Conn.Open
Rs.Open SQL, Conn or Conn.Execute(SQL)
tried changing or taking out the NetLib parameter from the connection string. I still get the same error. Also tried using Microsoft suggestion of using {Call Storedprocedure('parm','parm')} instead of an execute statment...same error results.

Is there something special you have to do when selecting from a temp table from a stored procedure then returning it?

MDAC Version is 2.8

All of my regular Select type SQL Queries work just fine using the SQLOLEDB connection string specified above...so far I am only having problems with this one stored procedure.

Thanks for any help.

JR

 

[JamesLean]

Add SET NOCOUNT ON at the top of your procedure? This stops the "x row(s) affected" messages being returned which OLEDB sees as extra recordsets.

--James

 

[JR4VB]

Awesome! Worked Great.