Up Prev Next
From: "Wechsler, Steven M" <WechslerSM at bernstein dot com>
Subject: RE: Strange transaction behavior
Date: May 20 1998 3:22PM
> -----Original Message-----
> From: Tim_Green@mercer.com [SMTP:Tim_Green@mercer.com]
> Sent: Wednesday, May 20, 1998 9:13 AM
> To: SybPerl Discussion List
> Subject: RE: Strange transaction behavior
> > Is there something unusual you need to do in order to be able to use
> > begin tran/commit tran with DBLib?
> > Here's what I'm doing:
> > begin tran
> > do a bunch of ad-hoc updates to user tables in various databases
> > do a bunch of updates to system tables via stored procedures
> > commit tran
> > This works fine in ISQL. It also works without the begin
> > tran
> > However, when I add in the begin tran & commit tran, the
> > ad-hoc updates
> > don't get applied, and the stored procedure updates get
> > applied whether
> > or not I commit.
> I suspect you're getting an error on one of the ad-hoc updates,
> causing Sybase
> to rollback the transaction. Since you said NONE of the ad-hoc
> updates get
> applied, I suspect the error occurs on the last update statement.
> Since you're
> not be detecting it, after Sybase rolls back the stored procedures
> that follow
> run outside an explicit transaction so become successfully committed.
> If the SQL is executed one query at a time, make sure you've got your
> error and
> message handlers defined. You're probably missing the error
It turned out that this was the case. There were triggers on some user
tables that were causing some updates to fail, and thus force the
transaction to be rolled back (I don't know if *all* the updates failed;
with my test data most of the updates end up updating 0 columns anyway).
However...(this is probably more a Sybase question, but...) I don't want
to rollback the transaction on failure of the ad-hoc updates; only if
the stored procedure updates fail. Is there anyway I can tell the SQL
server to *not* rollback the transaction automatically on an error?
Steven Wechsler/Sybase DBA/Sanford C. Bernstein & Co., Inc.
"Never underestimate the power of human stupidity" - Lazarus Long
Support the Anti-SPAM amendment: http://www.cauce.org