in what situation we use transaction tell me any one please

I had necer used BEGIN TRAN ... in SSIS Packages, but I think that is the same princip. BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency. Each transaction lasts until either it completes without errors and COMMIT TRANSACTION is issued to make the modifications a permanent part of the database, or errors are encountered and all modifications are erased with a ROLLBACK TRANSACTION statement. Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

what transactions we mostly used in our projects in ssis, dtc or native

what transactions we mostly used in our projects in ssis, dtc or native That depends entirely on the requirement there is no such fixed rule.My Blog | Ask Me

tell me the situation when we go for dtc and when we go for native transaction

Here is a simple SSIS example of a transaction: http://microsoft-ssis.blogspot.com/2011/01/ssis-transactions.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

---tell me the situation when we go for dtc and when we go for native ---transaction For example I need to get a MAX value and then user that value later on for updating BEGIN TRAN DECLARE @par INT SELECT @par =MAX(col) FROM tbl WHERE .. UPDATE tbl1 SET col=@par WHERE COMMIT TRAN Another example taken from BOL Examples This example causes a foreign key violation error in a transaction that has other Transact-SQL statements. In the first set of statements, the error is generated, but the other statements execute successfully and the transaction is successfully committed. In the second set of statements, the SET XACT_ABORT setting is turned ON. This causes the statement error to terminate the batch and the transaction is rolled back. CREATE TABLE t1 (a int PRIMARY KEY) CREATE TABLE t2 (a int REFERENCES t1(a)) GO INSERT INTO t1 VALUES (1) INSERT INTO t1 VALUES (3) INSERT INTO t1 VALUES (4) INSERT INTO t1 VALUES (6) GO SET XACT_ABORT OFF GO BEGIN TRAN INSERT INTO t2 VALUES (1) INSERT INTO t2 VALUES (2) /* Foreign key error */ INSERT INTO t2 VALUES (3) COMMIT TRAN GO SET XACT_ABORT ON GO BEGIN TRAN INSERT INTO t2 VALUES (4) INSERT INTO t2 VALUES (5) /* Foreign key error */ INSERT INTO t2 VALUES (6) COMMIT TRAN GO /* Select shows only keys 1 and 3 added. Key 2 insert failed and was rolled back, but XACT_ABORT was OFF and rest of transaction succeeded. Key 5 insert error with XACT_ABORT ON caused all of the second transaction to roll back. */ SELECT * FROM t2 GO DROP TABLE t2 DROP TABLE t1 GO Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

If you are having a sequence of statements or tasks to be executed, and the cycle of execution will be completed when all the statements are executed. If any one statement fails then the statements executed should also be rolled back. In these type of scenarios we use Transactions. Best example will be the BANK Transactions.

hi Uri, slightly OT. I'm always not quite sure about this example: BEGIN TRAN DECLARE @par INT SELECT @par =MAX(col) FROM tbl WHERE .. UPDATE tbl1 SET col=@par<mailto:col=@par> WHERE COMMIT TRAN Is there normally not a WITH ( TABLOCKX ) needed? E.g. The first task: USE AdventureWorksLT ; BEGIN TRANSACTION ; DECLARE @max MONEY ; SELECT @max = MAX(ListPrice) FROM SalesLT.Product WITH ( TABLOCKX ) ; WAITFOR DELAY '00:00:10' ; -- Without the above TABLOCKX the table can change. SELECT @max AS [before] , MAX(ListPrice) AS [after] FROM SalesLT.Product ; ROLLBACK TRANSACTION ; The concurrent task: USE AdventureWorksLT ; BEGIN TRANSACTION ; ---- The original list price value: 1431.50 DECLARE @max MONEY = 1234567 ; UPDATE SalesLT.Product SET ListPrice = @max WHERE ProductID = 680 ; COMMIT TRANSACTION ; To avoid the concurrency situation? Microsoft MVP Office Access https://mvp.support.microsoft.com/profile/Stefan.Hoffmann