Solved

ROLLBACK if @@error > 1

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 35
How to disable/enable multiple sql jobs in efficient way 11 98
SQL Server 2012 express 24 34
sql query help 2 43
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
A short film showing how OnPage and Connectwise integration works.

943 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

7 Experts available now in Live!

Get 1:1 Help Now