[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Error in transaction -> no rollback!

Posted on 2001-08-15
4
Medium Priority
?
878 Views
Last Modified: 2010-08-05
I have the following stored procedure: (ASE 11.9 on AEX)

create procedure test1
as
   begin transaction
   insert into testtable1 values (1)
   insert into testtable1 values (2)
   insert into testtable1 values (2)   <-- key violation
   insert into testtable1 values (3)
   commit transaction
go

If I define the field as primary key, statement 3 will fail off course. But the transaction is not aborted nor rollbacked! In other words: afterwards my table will contain:
1
2
3

Off course, this is not what I want. Checking @@rowcount is not an option, since I also have update statements (It's possible that the statement updates 0 rows without errors)

I want my transaction to do everything succesfull OR nothing at all. Any suggestions?
0
Comment
Question by:leon321
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 10

Accepted Solution

by:
bret earned 200 total points
ID: 6389522
Check @@error and/or @@transtate after each insert, and issue a rollback
if desired based on the results.  Some errors have severities such that they cancel the statement,
but not the transaction (these are indicated by @@transtate = 2 after the
operations).
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6390628
I am not sure but the Sybase does have a option "ignore duplicate keys" with CREATE UNIQUE INDEX option. Are you sure that you have defined the primary key constraint or created a unique index?.
0
 
LVL 5

Expert Comment

by:amitpagarwal
ID: 6391409
there is a good possibility in what matharva says.

just check if the create index definition has the ignore_dup_key attached to it ?
0
 

Author Comment

by:leon321
ID: 6397291
bret,

the "check @@transtate=2" comment works for me! Only drawback is that I have to check after each statement in my transaction :(

So I will accept your answer. However, since I finally have the flow in my procedures as I wanted, I get the following error when using this procedure recursively. (I call procedure test1 in procedure test1) :

"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing (previous count = 1, current count = 0)
Any thoughts??

Thanks!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question