Subject Re: [IBO] Too many SavePoints error
Author paultugwell
--- In, Helen Borrie <helebor@t...> wrote:
> At 10:00 AM 17/03/2004 +0000, you wrote:
> >--- In, Helen Borrie <helebor@t...>
> >
> > > You didn't mention a script, you mentioned
commitretaining...but if
> >you
> > > have a statement in your script that operates on 75,000 rows,
> >commits,
> > > operates on 100,000 rows, commits...that's too many operations
> >transaction.
> > >
> > > Helen
> > >
> >
> >I thought that the COMMIT statement would close the transaction and
> >the next update statement would start a new transaction. Is this
> >the case?
> Yes, it's the case. But it's not the number of *statements* in the
> transaction, it's the number of rows updated.
> >If so, what would be the best way to run a script like this?
> With a TIB_Script, that you invoke in such a way that you can
control how
> many rows are affected in each single transaction. A script is
just a
> string of statements so that probably means doing parser jiggery-
pokery in
> your client app to partition your batches and rebuild the script
> time. You can't control things like rowcounts and parameters. Why
> script and not a stored procedure?

I used a script because it is a one-off updating of a database
structure, and I have always done it this way before without
problems, although the database has not been this big before. Funiily
enough, the problem actually occoured on a table with about 80,000
rows. The largest table was updated without any problems. Why would a
stored procedure be better?