.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
sivanagamahesh

Post New Resource Bookmark and Share   

How to use rollback and commit in sql for multiple database

Posted By:Shashi Ray       Posted Date: October 19, 2015    Points: 200    Category:    URL: http://www.dotnetspark.com  

How to use rollback and commit in sql for multiple database.
 

There is two database (1). Current database and (2). nccmis_test database.

If you want to insert some value in both database, then this article is very useful for this.

 

COMMIT Statement

The COMMIT Statement terminates the current transaction and makes all changes under the transaction persistent. It commits the changes to the database. If a transaction is successful, commit it. A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. A COMMIT also frees resources, such as locks, used by the transaction.  The COMMIT statement has the following general format:

COMMIT TRANSACTION transaction_name

 

ROLLBACK Statement

The ROLLBACK Statement terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database. If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. A ROLLBACK also frees resources held by the transaction. The ROLLBACK statement has the following general format:

ROLLBACK TRANSACTION transaction_name



CREATE PROCEDURE [dbo].[usp_bvlms]

@store_name nvarchar (50),
@store_type nvarchar (50),
@state_name nvarchar(50),
@common_msg nvarchar(50) output

AS
BEGIN TRANSACTION Tran1
begin try

SET NOCOUNT ON
begin 

Insert into tbl_bvlms_mst (store_name, store_type, state_name) valuse (@store_name ,@store_type ,@state_name )
--Current Database

Insert into nccmis_test.dbo.tbl_nccmis_test (store_name, store_type, state_name) valuse (@store_name ,@store_type ,@state_name )
--Second Database (nccmis_test)

Insert into nccmis_test.dbo.tbl_common_test (bvlms_id,nccmis_id) values (@bvlms_id, @nccmis_id )
--Second Database (nccmis_test)

end
end try

begin catch
if @@TRANCOUNT>0
ROLLBACK TRANSACTION Tran1
end catch


if @@TRANCOUNT>0
COMMIT TRANSACTION Tran1



Thanks
Shashi Ray

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend