How to rollback a transaction in TSQL when string data is truncated?
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
3 Solutions collect form web for “How to rollback a transaction in TSQL when string data is truncated?”
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
How about turning on xact_abort
set xact_abort on
I would also point out that if you are receiving this error often, you need to revise the size of the column you are entering data into or adjust your cleaning process to prep the data before putting it into the prod table. In SSIS, You could also have the data that deosn’t meet the standard size go to a bad data table and process the rest.