If i update or delete some set of record then how do i rollback the changes. Is there any query for it. for e.g. if i run the following query: Update salesproposal set statuscode = 4 where so_id = 47888 How do i rollback this query in SQL Server. Thanks for your responses!!!
Rollback does work for DML(insert,update,delete). The rollback shud have a "begin tran" Try this: begin tran test Update salesproposal set statuscode = 4 where so_id = 47888 /* at this point if u do a select om ur table salesproposal u shud see the changes now u can either do a "commit tran" or "rollback tran" . If u do a commit changes will be made permanent or if rollback changes will be rolled back */ rollback tran test /* now ur select statement will give u the result that was before the update statement. Hope this answrd ur question. Let me now if anything.
Hi Sam, The above said rollback works only once irrespetive of earlier queries exectuted with begintran. For Ex:- If I run 10 Queries in different time intervals using Begin Tran test, I cannot run the roll back test for those queries in different time intervals. If I rollback First query out of 10 queries, it works. Next when I try for second query, it gives message "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." Is there any solution for this. I am using SQL2000
You need be more clear on nested transactions then, say with the BEGIN TRANSACTION statement increments the @@TRANCOUNT Server Variable with 1, and the COMMIT TRANSACTION decrements the @@TRANCOUNT variable with 1. The ROLLBACK TRAN statement however, decrements the @@TRANCOUNT server variable to 0. That's why the last COMMIT statement gives us the error message: there has been a rollback, and therefore the @@Trancount is set to zero. Apparently, the ROLLBACK TRANSACTION also rollbacks to the most outer begin transaction, it is not possible to use nested transactions in this way. The way out is to use SAVE POINTS, you start a transaction, execute a statement, and save the transaction using the SAVE TRANSACTION sp1 statement. This statement sets a savepoint with the name 'sp1'. You can then rollback to that savepoint using the ROLLBACK TRAN <savepointname> command. Further information on nested transactions can be obtained from updated books online.
[quote user="subramanivg"] Hi Sam, The above said rollback works only once irrespetive of earlier queries exectuted with begintran. For Ex:- If I run 10 Queries in different time intervals using Begin Tran test, I cannot run the roll back test for those queries in different time intervals. If I rollback First query out of 10 queries, it works. Next when I try for second query, it gives message "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." Is there any solution for this. I am using SQL2000 [/quote] Nested transactions is one of the thing I yet have to find a valid reason for. In most cases you don't need this as you can check @@TRANCOUNT if you are already in a transaction or not. And if you are, then there is no need to start another one. This will also save you from these error you experience now.