How to ROLLBACK TRANSACTION on client level
-
Suppose i connect to db (SQL Server) from the client (.NET) and call to some store procedure, in this sp i start transaction (BEGIN TRANSACTION) and before COMMIT or RALLBACK an error happen that imidietly stop the execution of stored procedure, In the client i cacth this error but what about an open transaction ??? HOW to rollback in the client??? ------------------------------------ To study, study and only to study
-
Suppose i connect to db (SQL Server) from the client (.NET) and call to some store procedure, in this sp i start transaction (BEGIN TRANSACTION) and before COMMIT or RALLBACK an error happen that imidietly stop the execution of stored procedure, In the client i cacth this error but what about an open transaction ??? HOW to rollback in the client??? ------------------------------------ To study, study and only to study
I am not an expert in SQL by any means, and so I don't know if it is possible to do what you want. But if you could start you transaction on the client side, then you could undo everything. See
SqlTransactionclass. -- LuisR
Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!
-
Suppose i connect to db (SQL Server) from the client (.NET) and call to some store procedure, in this sp i start transaction (BEGIN TRANSACTION) and before COMMIT or RALLBACK an error happen that imidietly stop the execution of stored procedure, In the client i cacth this error but what about an open transaction ??? HOW to rollback in the client??? ------------------------------------ To study, study and only to study
Short answer: try SET XACT_ABORT ON...
"If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. It is the responsibility of the programmer to code the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs."-- SQL Server Books Online A better answer would be to include error handling. There's a very good article here[^] that describes one strategy for getting more predictable results from stored procedures through error handling.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-
Short answer: try SET XACT_ABORT ON...
"If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. It is the responsibility of the programmer to code the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs."-- SQL Server Books Online A better answer would be to include error handling. There's a very good article here[^] that describes one strategy for getting more predictable results from stored procedures through error handling.The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’