Menu
Currently I have a large import process that I'm trying to wrap inside a transaction so if anything breaks - i could rollback. The issue I have is that when the TSQL inside the trans blows up, it won't rollback when the following SQL error occurs
Msg 8152, Level 16, State 14, Line 249 String or binary data would be truncated. The statement has been terminated. The below wraps this import TSQL
DECLARE @error INT SELECT @error = 0 BEGIN TRANSACTION --** begin import TSQL --** end import TSQL SELECT @error = @@error IF @error != 0 GOTO handle_error COMMIT handle_error: IF @error != 0 BEGIN ROLLBACK END 
531
You cannot ROLLBACK TRUNCATE Simply, you cannot rollback a transaction if it is already committed but you can do something else to get the data back (or at least some parts of it). When you execute the TRUNCATE statement, your data is still in the MDF file.
When you execute a Truncate statement, it does not get logged in the log file as it is a DDL statement. So if you Truncate a table, you cannot Roll Back to a point in time before the truncate. However, in a Transaction, Rollback is permitted and functions just as any other rollback would.
We can rollback the data in conditions of Delete, Truncate & Drop. But must be used Begin Transaction before executing query Delete, Drop & Truncate.
After you commit the transaction, the changes are visible to other users' statements that execute after the commit. You can roll back (undo) any changes made during the transaction with the ROLLBACK statement (see ROLLBACK.
If your on SQL 2005 you can try:
BEGIN TRANSACTION BEGIN TRY --Run your Statements COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION DECLARE @Msg NVARCHAR(MAX) SELECT @Msg=ERROR_MESSAGE() RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG END CATCH If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
