Author |
Topic |
scullee
Posting Yak Master
103 Posts |
Posted - 2003-03-16 : 23:20:41
|
I have a stored procedure that performs and import and i have the whole thing working fine.When there is an error in the import (eg Duplicate Record created) the system logs it to a table then at the end it emails the failures.This works really well as a stored procedure run through query analyzer and everything works fine, if it has an error the processing continues without a problem.I was happy with the whole process so i set the job up to schedule the running. The problem now is that as soon as the stored procedure has an error (the one im getting is a duplicate key (severity 16)) it exits and fails the job when i want it to continueHere is the error log Violation of PRIMARY KEY constraint 'PK_MEMBER'. Cannot insert duplicate key in object 'MEMBER'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed. Edited by - scullee on 03/16/2003 23:21:54 |
|
samsekar
Constraint Violating Yak Guru
437 Posts |
Posted - 2003-03-17 : 02:07:14
|
Can you post the SP? Iam sure that somebody can help you.Sekar~~~~Success is not a destination that you ever reach. Success is the quality of your journey. |
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2003-03-17 : 18:07:42
|
I know i can fix the problem thats occuring by checking to see if the value is already in the database before i try the import but I would prefer that i do an insert and then handle the error if it fails.In not expecting it to fail often (if at all) so the insert should rarely fail but i need to test the functionality in case we do get a dodgy record.Anyway, here is the part of the SP that is failing. BEGIN select @Added=@Added+1 Print 'Inserting Member No ' + @Member_No INSERT INTO MEMBER (MEMBER_NO,SURNAME,MEMBER_TYPE,UPDATED) VALUES (@MEMBER_NO,@SURNAME,@MEMBER_TYPE,GETDATE()) select @Err = @@Error if @Err <> 0 begin insert into ##Export (Results) values (@Member_No + ', Insert Failure ' + ' Error Code = ' + cast(@Err as varchar)) select @Errors=@Errors+1 end END My question is more about how come the behavior under Query analyser is different to when you run it through a scheduled job. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-17 : 18:46:21
|
Think theres something strange with the code or setup.try thiscreate table zz (i int primary key)goinsert zz select 1gocreate table zzaudit (s varchar(1000), id int identity)gocreate proc zzinsasinsert zz select 1 insert zz select 1if @@error <> 0 insert zzaudit select 'failed'elseinsert zzaudit select 'success'gonow create a job withexec zzinsrun it and you should see a failed message in zzaudit.If the failure is in a trigger then it will terminate immediately - but you should see the same thing from query analyser.You should never assume that all errors are trappable as the connaction may be terminated at any time.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
scullee
Posting Yak Master
103 Posts |
Posted - 2003-03-17 : 19:01:20
|
I did it and it is inserting the records into the database but saying the job failed so it is a little different. Violation of PRIMARY KEY constraint 'PK__zz__5E0518CE'. Cannot insert duplicate key in object 'zz'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Violation of PRIMARY KEY constraint 'PK__zz__5E0518CE'. Cannot insert duplicate key in object 'zz'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed. I might just add the code in there to check to see if the record exists before i do the insert.I just would prefer to do the blind insert and only perform error handling when i have to because i am not expecting this to have errors very often.Oh how i am hanging out for Yukon expecially if it allows proper error handling in stored procedures :)Thanks for the help. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-17 : 19:17:40
|
The point is that both my code and yours are getting a primary key violation but mine is processing the following statements but yours is not.You need to find out what is different with your code which is preventing this.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
mazikhan
Starting Member
4 Posts |
Posted - 2003-03-28 : 16:38:59
|
Hi there,I posted a new topic on the forum, but after further searching I found that this topic was directly relevant to my question. I noticed that 'nr' had stated that primary key violations are fatal errors if generated from within a trigger. I am actually doing some processing in an 'after' trigger which writes to another table (where the primary key violation may occur). Is there anyway to salvage this operation? Like the original poster, I too am doing many inserts and expect rare violations (1 in 100K), so I do not want to first check to see if the key already exists. Also, since I DO want to replace the offending row, I don't want to attack the problem by making a unique constraint and ignoring dupes. Is it possible to circumvent this by calling a stored procedure which does the insert (and handling the error in the stored procedure)?Any input/suggestions would be appreciated. Note that I am NOT looking to do a lookup first. I understand that this is a solution, but it is one that I would prefer to reserve as a last resort.Regards,Mazi. |
|
|
mazikhan
Starting Member
4 Posts |
Posted - 2003-03-28 : 17:03:45
|
I followed up on the logic that 'nr' had stated - that a duplicate key violation from a trigger would be fatal. I put the insertion into a stored procedure, and had the sp do the error handling, if any. This solution seems to work.So, I owe 'nr' my gratitude for pointing out this issue.Thanks 'nr'Mazi. |
|
|
mazikhan
Starting Member
4 Posts |
Posted - 2003-03-28 : 17:16:05
|
I am sorry to have to eat my words! It seems as if my co-worker had truncated the table into which I was inserting the duplicate rows, so no violation was taking place. The problem persists, so I would appreciate any solutions on offer.Thanks yet again.Mazi. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-28 : 17:22:28
|
mazikhan, in order to help, we're going to need to see part of the stored procedure (the error handling part and the insert part).Tara |
|
|
mazikhan
Starting Member
4 Posts |
Posted - 2003-03-28 : 18:15:33
|
Thank you for your input.The statements are actually being run from an 'after trigger'. I moved them to a stored procedure (and then had the trigger call the stored procedure) to see if it made any difference (yes, I am desperate). It did not, so it is back in the trigger.The following is the code that is generating the errors.INSERT INTO clog VALUES (@TransNr, @Cargo, .....); -- these 2 are the keys that are read in from the inserted tableIF (@@Error <> 0) BEGIN UPDATE clog SET ..... WHERE TransNr = @TransNr and Cargo = @Cargo; IF (@@Error <> 0) BEGIN INSERT INTO errorlog( "some record of failure"); ENDENDRegards,Mazi. |
|
|
|