Solved

ROLLBACK if @@error > 1

Posted on 2009-05-05
2
166 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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