Re: aout cursor staoed procedure and vb6 (ado)

Ah no, this won't work as you expect. A SP won't return intermediate results
as you seem to think. If you want a progress bar you need to make an async
call to execute the SP (as I describe in my books), and fire a timer
periodically which bumps the progress bar. How do you know how long the
process will take? It's usually not known ahead of time--only past
experience can tell you how long it usually takes.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________


"Tom" <pclan@xxxxxxxxxxxx> wrote in message
news:uy81GmIcFHA.584@xxxxxxxxxxxxxxxxxxxxxxx
> Thanks for your advice , I correct stored procedure name of prefix
> however, I just want to get value from cursor of stored procedure,that I
> can
> do progress bar or status to show
> about the running status from sql server. Since, I don't want put the
> procedure into application layer
> (ie.e loop recordset and run)Therefore, I try to find the code to trace
> cursor on runing row by row.
>
>
> any idea about this ?
>
> Thanks for your help
>
> "William (Bill) Vaughn" <billvaNoSpam@xxxxxxxxx> ¦b¶l¥ó
> news:OgPEKRIcFHA.1148@xxxxxxxxxxxxxxxxxxxx ¤¤¼¶¼g...
>> 1) Don't prefix your SPs with "SP_". It forces the server to look for
>> your
>> SP in the master database--this hurts performance.
>> 2) The RETURN value is not an OUTPUT parameter (not really). You have to
>> mark the Parameter as a adParameterReturnValue.
>> 3) Never touch (reference) the Parameters collection as you look for
> RETURN
>> value or OUTPUT parameters until the rowset population is complete. It
>> can
>> dent the objects and make them impossible to reference later (old bug
> might
>> have been fixed). In any case the RV won't be available until you close
> the
>> Recordset used to capture the rowset.
>> 4) I have no idea what you're trying to accomplish with this SP. Why use
>> a
>> server-side cursor? Where did you see an example that showed how to do it
>> this way?
>>
>>
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com/blog/billva
>> www.betav.com
>> www.sqlreportingservices.net
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> __________________________________
>>
>>
>> "Tom" <pclan@xxxxxxxxxxxx> wrote in message
>> news:%23R2OeJIcFHA.2424@xxxxxxxxxxxxxxxxxxxxxxx
>> > Hi : I create the simple cursor stored procedure as follow :
>> > CREATE PROCEDURE SP_CURSORSAMPLE
>> > @retValue nvarchar(15) output
>> >
>> > AS
>> > DECLARE @iAcID INT,
>> > @nSumAmount Money,
>> > @sType nVarchar(50),
>> > @iDetailID INT
>> >
>> > DECLARE curBsDetail CURSOR FOR
>> >
>> > SELECT sTenantCode
>> > FROM tenantprofile
>> >
>> > OPEN curBsDetail
>> > FETCH NEXT FROM curBsDetail INTO
>> > @retValue
>> >
>> > WHILE @@FETCH_STATUS = 0
>> > BEGIN
>> >
>> > print @retValue
>> >
>> > FETCH NEXT FROM curBsDetail INTO
>> > @retValue
>> > END
>> >
>> > CLOSE curBsDetail
>> > DEALLOCATE curBsDetail
>> >
>> > if I use ado.command to execute how can I get return value of retValue
>> > during the command is execute : I try to write the similar vb code but
> it
>> > not work
>> >
>> > Dim Cmd As Command
>> > de.cnn.Open
>> > Set Cmd = New Command
>> > With Cmd
>> > .CommandText = "SP_CURSORSAMPLE"
>> > .CommandType = adCmdStoredProc
>> > .ActiveConnection = de.cnn
>> > .Parameters.Append .CreateParameter("retValue", adVarWChar,
>> > adParamOutput, 15)
>> > .Execute , , adAsyncFetch '<--- start ASYNCHROUS
>> >
>> >
>> > Do While (.State And adStateExecuting) = adStateExecuting
>> > Debug.Print .Parameters("retValue")
>> > Loop
>> >
>> > End With
>> > can anyoneresolve my problem ?
>> >
>> > Thanks
>> >
>> >
>>
>>
>
>


.

Relevant Pages

  • Re: aout cursor staoed procedure and vb6 (ado)
    ... I correct stored procedure name of prefix ... cursor on runing row by row. ... > 2) The RETURN value is not an OUTPUT parameter. ... >> DECLARE curBsDetail CURSOR FOR ...
    (microsoft.public.data.ado)
  • Re: aout cursor staoed procedure and vb6 (ado)
    ... The RETURN value is not an OUTPUT parameter. ... mark the Parameter as a adParameterReturnValue. ... Never touch (reference) the Parameters collection as you look for RETURN ... > OPEN curBsDetail ...
    (microsoft.public.data.ado)