Solved

ROLLBACK if @@error > 1

Posted on 2009-05-05
2
164 Views
Last Modified: 2012-05-06
I have a code that does something an I would like to put it into a trasaction and if it give me an error then I would like to do a ROLLBACK and send an email.

Coul you recommend me the best way of doing it in SQL 2005

Thank you in advance
0
Comment
Question by:amedexitt
2 Comments
 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24311183
you want something like this:
Create Procedure yourProcedure

AS

BEGIN TRY

	BEGIN Transaction;
 

	-- Put your code here
 

	COMMIT Transaction;

END TRY

BEGIN CATCH

	IF @@TRANCOUNT > 0

		ROLLBACK;
 

	EXEC msdb.dbo.sp_send_dbmail

@recipients=N'user@domainl.com',@body='Message Body', 

@subject ='Message Subject'
 

END CATCH

Open in new window

0
 

Author Closing Comment

by:amedexitt
ID: 31578320
Perfect
Thank you very much
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now