Re: aout cursor staoed procedure and vb6 (ado)

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)
    ... If you want a progress bar you need to make an async ... > however, I just want to get value from cursor of stored procedure,that I ... >> 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)
  • Re: Stored Procedure, Output Value with SELECT Statement
    ... you are best off with the Set NoCount On in your procedure. ... your output parameter will only be available to your client code ... after you have read all of the select cursor on your client connection. ... load the recordset into an array or something so that you can get to ...
    (microsoft.public.data.ado)
  • Re: SP - output parameter not coming back
    ... The client side cursor did not help. ... If I do not find out about the output parameter I am considering ...
    (microsoft.public.inetserver.asp.db)
  • Re: Passing a table or cursor into a stored procedure
    ... Cursor can be used as OUTPUT parameter only not as Input-Output. ... cursor will alow stored procedure to return a cursor as output. ... >> Hari Sharma ...
    (microsoft.public.sqlserver.programming)