Re: Recordset error when executing stored procedure (#3704)
- From: "Val Mazur \(MVP\)" <group51a@xxxxxxxxxxx>
- Date: Fri, 15 Apr 2005 22:15:05 -0400
Hi 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" <SeanHarrop@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D463CB40-B978-4A60-B924-5EDFB3585A42@xxxxxxxxxxxxxxxx
> Hello 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:
>
>> 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?
.
- References:
- Recordset error when executing stored procedure (#3704)
- From: Sean Harrop
- RE: Recordset error when executing stored procedure (#3704)
- From: Sean Harrop
- Recordset error when executing stored procedure (#3704)
- Prev by Date: Re: data error event hit :[ADODC]: Unknown error. [ADO]:
- Next by Date: Re: How to improve performance in VB6
- Previous by thread: RE: Recordset error when executing stored procedure (#3704)
- Next by thread: RT Error 713
- Index(es):
Relevant Pages
|
