@@error trap not working

[Eyespi20]

I am trying (so far unsucessfully) to use the @@error function to trap errors, record them and send the result set to me via email so I can "correct" the bad data in the specific rows.

declare @error int 
set @error =0
BEGIN TRANSACTION
insert into request_event select
	externalrefid,
	sequence,
	security_code,
	request_line,
	req_line_sequence,
	event_type,
	event_type_reason,
	event_dt,
	event_tm,
	customer_event_dt,
	customer_event_tm,	
	person_id_from,
	person_id_to,
	description,
	dur_minutes,
	value_from,
	value_to,
	value_type,
	promised_begin_dt,
	promised_begin_tm
from
	newevent2
where
	chk=0


IF @@ERROR <>0 begin
	set @error=2
end

IF @Error = 0 Begin
	-- Update worked
	Commit Tran
End
Else Begin
	-- Error

update econupdate set chk=2 
from econupdate join newwork on econupdate.externalrefid=newwork.externalrefid
and econupdate.econupdate_identity=newwork.updateindex

declare @query varchar (255)

set @query = 'select * from econupdate where chk=2'

exec master..xp_sendmail
	@recipients='margaretn@ttc-inc.com',
	@subject='Event insert step failed',
	@query=@query,
	@attach_results='true',
	@width=250


	Rollback Tran A

end

My problem is, the email never gets sent and the rows that should be marked are not.

I'm sure it's something really simple that I'm missing, so please excuse my ignorance here.

Thanks in advance for your help on this issue.

Margaret

 

[SQLDenis]

some things like foreign key/primary key constraints violations are not trapable and the transaction will blow up

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[Eyespi20]

Ok - so although the JOB fails because of trying to insert a duplicate row (the most common cause of failure for this particular insert), you're saying I cannot trap it?

BUMMER!

Margaret

 

[SQLDenis]

read

http://www.sommarskog.se/error-handling-II.html

and

http://www.sommarskog.se/error-handling-I.html

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[mrdenny]

Before SQL 2005 SQL Server didn't have actual error trapping. Errors are raised and based on the severity of the error the batch is allowed to continue or not. If the severity was such that the batch was allowed to continue you can see if an error occured by using the @@ERROR variable, however as Denis said if a high level error occured then the batch will be stopped in it's tracks and SQL will never get to your @@ERROR command.

In SQL 2005 we now have TRY CATCH blocks much like in other programing languages and we can now do proper error catching.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Lamprey13]

I might be off base as I just looked at this real quick, but you are doing a Rollback after sending the mail, which might rollback the sending of the mail. You could try doing the rollback, then sending the mail and seeing if that works. But, I think the batch is probably terminated so that probably will not work.

 

[Eyespi20]

Alas, this is a 7.0 database, so no access to try catch

as for the rollback being after the email is sent, when I had it before the email, the email still wasn't sent.

Also, I'm telling it to rollback transaction A which *should* mean it only rolls back the insert.

Since the main source of crap-out on this insert is violation of the primary key, it is probably not getting to the @@error and so, I am screwed.

thanks everyone.

Margaret

 

[rac2]

Well if it were just the primary key and the issue was duplication or it is a foreign key which does not exist you could check that before doing the insert. If you find the primary key value already exists, or that the foreign key does not exist, then send the email, otherwise do the insert.