How can I cancel an UPDATE query on SQL Server 2005?

Question

How can I cancel an UPDATE query on SQL Server 2005?

I need to do this in SQL, not through code.

Answer 1

begin transaction // execute SQL code here rollback transaction 

If you have already completed the request and want to return it, unfortunately, the only real option is to restore the backup copy of the database. If you are using full backup, you should be able to restore the database at a specific point in time.

Answer 2

You need this tool, and you can find the transaction and cancel it.

ApexSQL Journal

Answer 3

You can use implicit transactions to do this.

 SET IMPLICIT_TRANSACTIONS ON update Staff set staff_Name='jas' where staff_id=7 ROLLBACK 

As you ask, you can set this setting ( SET IMPLICIT_TRANSACTIONS ON ) from the stored procedure by setting this stored procedure as the start procedure.

But the SET IMPLICIT TRANSACTION ON command is connection specific. This way, any connection other than the one that starts the stored startup procedure will not use the setting you set.

Answer 4

You can cancel the statements that you executed in the transaction. Instead of committing a transaction, rollback the transaction.

If you have updated something and want to cancel these updates, and you have not done this inside the transaction (not yet completed), I think this is luck ...

(manually restore or restore backups)

Answer 5

Once the update is complete, you cannot undo only one update. It is best to revert to a previous database backup.

Answer 6

From the information you provided, your best chance of recovery is backing up your database. I don’t think that you can roll back any of these changes that you pushed, because at that time you apparently didn’t use transactions.

Answer 7

Simplicity:

header code ...

 Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = New ADODB.Recordset On Error GoTo ERRORHAND 

Work code ...

 objMyConn.ConnectionString = ConnStr objMyConn.Open 

code ....

'Copy data from Excel'

 objMyConn.BeginTrans <-- define transactions to possible be rolled back For NewRows = 2 To Rows objMyRecordset.AddNew For NewColumns = 0 To Columns - 1 objMyRecordset.Fields(NewColumns).Value = ActiveSheet.Cells(NewRows, NewColumns + 1) Next NewColumns objMyRecordset.Update Next NewRows objMyConn.CommitTrans <- if success, commit them to DB objMyConn.Close 

ERRORHAND:

 Success = False objMyConn.RollbackTrans <-- here we roll back if error encountered somewhere LogMessage = "ERROR writing database: " & Err.Description 

...

Answer 8

As already mentioned, you can do nothing but restore from a backup. At least now you will learn how to always transfer statements in transactions to see what happens before you decide to commit. In addition, if you do not have a backup of your database, it will also teach you how to make regular backups of your database.

Although we have not really helped your imdeiate problem ... hope these answers guarantee that you will not encounter this problem again in the future.

Answer 9

in this example, we start a 2-line insert into the query, and if all of them are true, it starts, but if you do not run anything and ROLLBACK

 DECLARE @rowcount int set @rowcount = 0 ; BEGIN TRANSACTION [Tran1] BEGIN TRY insert into [database].[dbo].[tbl1] (fld1) values('1') ; set @rowcount = (@rowcount + @@ROWCOUNT); insert into [database].[dbo].[tbl2] (fld1) values('2') ; set @rowcount = (@rowcount + @@ROWCOUNT); IF @rowcount = 2 COMMIT TRANSACTION[Tran1] ELSE ROLLBACK TRANSACTION[Tran1] END TRY BEGIN CATCH ROLLBACK TRANSACTION[Tran1] END CATCH 

Answer 10

Try

 ROLLBACK WORK; 

It usually works