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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
You may also want to look at this

http://msdn.microsoft.com/en-us/library/ms179296.aspx
0
DDBCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.