BEGIN TRANSACTION TRAN1
BEGIN TRY
INSERT INTO.....
UPDATE.....
END TRY
BEGIN CATCH
If @@TRANCOUNT > 0
--error happened
ROLLBACK TRANSACTION TRAN1
END CATCH
If @@TRANCOUNT > 0
COMMIT TRANSACTION TRAN1
GO
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Thursday, October 16, 2014
Friday, August 17, 2012
Error handling in transactions in sql 2008
BEGIN TRANSACTION TRAN1
BEGIN TRY
--put all the valid statements here--
CREATE TABLE #test(intREsult int);
INSERT INTO #test Values (4/0);
INSERT INTO #test Values (1/2);
COMMIT TRAN TRAN1
END TRY
BEGIN CATCH
--the error handling code including the rollback
DECLARE @errorMEssage nvarchar(1000);
SELECT @errorMEssage = ERROR_MESSAGE();
ROLLBACK TRAN TRAN1;
RAISERROR(@errorMEssage,16,1);
END CATCH
BEGIN TRY
--put all the valid statements here--
CREATE TABLE #test(intREsult int);
INSERT INTO #test Values (4/0);
INSERT INTO #test Values (1/2);
COMMIT TRAN TRAN1
END TRY
BEGIN CATCH
--the error handling code including the rollback
DECLARE @errorMEssage nvarchar(1000);
SELECT @errorMEssage = ERROR_MESSAGE();
ROLLBACK TRAN TRAN1;
RAISERROR(@errorMEssage,16,1);
END CATCH
Subscribe to:
Posts (Atom)