Recordset error when executing stored procedure (#3704)

Recordset error when executing stored procedure (#3704)

Post by U2VhbiBIYX » Fri, 15 Apr 2005 23:32:06


I am trying to execute a stored procedure and return the data to a recordset.
I have tried a number of different methods...

objRS.Open "EXEC up_procedure_name PARAM", objConn

and

Set objRS = objConn.Execute("EXEC up_procedure_name PARAM")

and

objCMD.CommandText = "up_procedure_name"
objCMD.CommandType = adCmdStoredProc
Set objParam = objCMD.CreateParameter("param", adInteger, adParamInput,
4)
objCMD.PARAMETERS.Append objParam
objParam.VALUE = intParam
objCMD.ActiveConnection = objConn
Set objRS = objCMD.Execute

All three of these methods appear to open the recordset, however when I
check to see if data has been returned to the recordset, I recieve
ADODB.Recordset error #3704 "Operation is not allowed when the object is
closed." The line that produces the error is

If Not objRS.EOF Then

When I check the objRS.State, it returns 0, which is means that it is, in
fact, closed. If this is the case, then why doesn't it error at objRS.Open?

When I run the stored procedure in Query Analyzer ("EXEC up_procedure_name
PARAMS"), I do receive data back, so I know that the procedure is working as
coded.

The stored procedure should be returning data from a temp table in SQL (i.e.
- CREATE TABLE #table_name) and a derived table. I don't think these facts
would have any outcome on how ADO interacts with the Stored Procedure and
Recordset. Here is some psudo code describing the stored procedure.

CREATE PROCEDURE up_stored_procedure
@parameter INT
AS

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

CREATE TABLE #temp
( field_1,
field_2,
field_3 )

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

INSERT INTO #temp
SELECT DISTINCT field_1, field_2, field_3 FROM real_table

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

UPDATE #temp SET field_3 = 0
FROM #temp
INNER JOIN
( SELECT * FROM #temp WHERE field_1 = 'x' and field_2 = 'y' ) AS
derived_temp
ON ( derived_temp.field_1 = #temp.field_1
AND derived_temp.field_2 = #temp.field_2
AND derived_temp.field_3 = #temp.field_3 )

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

SELECT DISTINCT temp_a.*
FROM #temp AS temp_a
INNER JOIN
( SELECT DISTINCT field_1, field_2, count(*)
FROM #temp
WHERE field_3 = 0
GROUP BY field_1, field_2
HAVING count(*) > 1 ) AS derived_temp
ON ( temp_a.field_1 = derived_temp.field_1
AND temp_a.field_2 = derived_temp.field_2 )
WHERE valid = 0
ORDER BY
temp_a.field_1, temp_a.field_2

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

GO

Does anyone have any ideas what would cause this problem?
 
 
 

Recordset error when executing stored procedure (#3704)

Post by U2VhbiBIYX » Fri, 15 Apr 2005 23:43:07

ello all... I revised my search in the newsgroups and found a solution that
works. For some reason, adding "SET NOCOUNT ON" at the very top of the
procedure allows the recordset to work when returning values from a SQL
Server #temp table. Just wanted to share so someone else doesn't waste a day
trying to figure that problem out.

-Sean

"Sean Harrop" wrote:


 
 
 

Recordset error when executing stored procedure (#3704)

Post by MV » Sun, 17 Apr 2005 11:15:05

i Sean,

This reason described in next KB.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q235340

--
Val Mazur
Microsoft MVP

http://xport.mvps.org



"Sean Harrop" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...