Errors And Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Errors And Stored Procedures

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 continue

Here 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 this

create table zz (i int primary key)
go
insert zz select 1
go
create table zzaudit (s varchar(1000), id int identity)
go
create proc zzins
as
insert zz select 1 insert zz select 1
if @@error <> 0
insert zzaudit select 'failed'
else
insert zzaudit select 'success'
go

now create a job with
exec zzins

run 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 table

IF (@@Error <> 0)
BEGIN
UPDATE clog
SET .....
WHERE TransNr = @TransNr and Cargo = @Cargo;
IF (@@Error <> 0)
BEGIN
INSERT INTO errorlog(
"some record of failure");
END
END

Regards,
Mazi.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources