SQL procuedure call - process doesn't terminate until I quit thescreen - VERY URGENT

[Syed Shahul Hameed Mustaffa]

Hi Peter,

I resolve the issue and this is just to share this knowledge.

I was repeatedly call a SQL SERVER stored procedure. I had misplaced a BEGIN
TRANSACTION.
So everytime I called the procedure, a transaction is started and left open.
I placed the commit within an 'if' structure. So after I moved the BEGIN
TRANSACTION inside the 'if' sturcture, the problem went away.

Thanks again for your tip.

Regards,
SYED.



On 1/19/06, Syed Shahul Hameed Mustaffa <sshahulgm at gmail.com> wrote:
>
> Thank you Peter for your suggestion.
>
> It didn't solve the issue.
> However, when I commented out some code from the sql stored procedure, the
> problem seems to have disappeared. I'll do some research in the sp.
>
> In the meanwhile if you can think of some tips, I'll appreciate.
>
> Regards,
> SYED.
>
>
>
> On 1/19/06, Peter Bateman <pfbcs at hotmail.com> wrote:
> >
> > I would try after calling my procedure
> > COMMIT UPDATE
> >
> > Regards,
> > Peter
> >
> >
> > >
> > >Dear Gurus,
> > >
> > > >From a SCREEN of Axiant 4GL/ PH 4GL/ WINDOWS, I am calling a SQL
> > SERVER
> > >2000
> > >stored procedures to create sql jobs.
> > >Even though the procedure execution completes successfully, the tables
> > that
> > >are updated by the procedure, gets locked and doesn't get released
> > until I
> > >quit the screen that call the sp and also the previous screen.
> > >In fact the SQL server process that is created for executing the stored
> >
> > >procedure doesn't end.
> > >It is blocking the other processes that try to read the locked tables.
> > >I'm calling the procedure from the update procedure.
> > >
> > >The procedure call code is as follows
> > >
> > >        sql in cwdb transaction update   &
> > >        call CreateJob (JNAME, JOBDESC, QZNAME , STEPEXECSEQ, PROGTYPE
> > &
> > >        PHDICT, QZPROG, QZPARMFILE, OKTOSTREAM, RUNATONCE,USEPARMFILE,
> > >USERID, SQLSPMESSAGE OUT )  &
> > >        ON ERROR CONTINUE
> > >
> > >I'd appreciate any help at the earliest in solving this issue.
> > >
> > >Regards,
> > >SYED.
> >
> >
> > >--
> > >= = = = = = = = = = = = = = = = = = = = = = = = = = = =
> > >Mailing list: powerh-l at lists.sowder.com
> > >Subscribe: "subscribe" in message body to powerh-l-request at lists.sowder.com
> >
> > >Unsubscribe: "unsubscribe <password>" in message body to
> > >powerh-l-request at lists.sowder.com
> > > http://lists.sowder.com/mailman/listinfo/powerh-l
> > >This list is closed, thus to post to the list you must be a subscriber.
> >
> > _________________________________________________________________
> > Take charge with a pop-up guard built on patented Microsoft(r) SmartScreen
> >
> > Technology
> > http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
> >
> > Start enjoying all the benefits of MSN(r) Premium right now and get the
> > first two months FREE*.
> >
> > --
> > = = = = = = = = = = = = = = = = = = = = = = = = = = = =
> > Mailing list: powerh-l at lists.sowder.com
> > Subscribe: "subscribe" in message body to
> > powerh-l-request at lists.sowder.com
> > Unsubscribe: "unsubscribe <password>" in message body to
> > powerh-l-request at lists.sowder.com
> > http://lists.sowder.com/mailman/listinfo/powerh-l
> > This list is closed, thus to post to the list you must be a subscriber.
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20060124/03849fea/attachment.html