Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ROLLBACK if @@error > 1

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

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.
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

860 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