Scheduling a t-sql transaction to auto commit and error handling

I have a sql transaction runs everyday as a sql job. At the moment it does not check any errors. Is there a way that I can script it to check if there is error during the transaction and commit or rollback according to the error existance and email a warning message on any error?

Many thanks,
Serdar DemirkolApplication Developer & DBAAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I would say that starting to read here us what you need to do:
http://msdn.microsoft.com/en-us/library/aa175920%28SQL.80%29.aspx
0
 
Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
You may also want to look at this

http://msdn.microsoft.com/en-us/library/ms179296.aspx
0
 
DDBConnect With a Mentor Commented:
Can you use it in procedure itself?
CREATE PROCEDURE YourProcedureName ()
AS

BEGIN TRANSACTION    -- Start the transaction

....YOUR CODE


-- check for errors
IF @@ERROR <> 0
  --  if @ERROR is not 0, rollback. You can put more details here if you wish
  ROLLBACK
ELSE
  COMMIT  
0
 
Serdar DemirkolApplication Developer & DBAAuthor Commented:
All are great articles on error handling.

Thanks,
0
 
Serdar DemirkolApplication Developer & DBAAuthor Commented:
Here is the solution I found for emailing the transaction outcome.
xp_stopmail
go
xp_startmail
go
xp_sendmail @recipients=N'mailuser@mail.com', 
@Subject=N'Failure',
@message=N'Job has failed.'

Open in new window

0
All Courses

From novice to tech pro — start learning today.