How to rollback a transaction in TSQL when string data is truncated?

Question

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

score 83 · Accepted Answer · answered Mar 12 '09 at 15:53

83

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

answered Mar 12 '09 at 15:53

JoshBerke

66,142
25
126
164

Yep had you not suggested it already I would have done that as my fall back answer ;-) – JoshBerke Mar 12 '09 at 17:17
Using this method - In my case the statements produced error and then those tables ended up getting locked because the transaction remained opened. I had to follow these instructions to remove the lock - https://stackoverflow.com/questions/8258710/how-to-solve-sql-server-error-1222-i-e-unlock-a-sql-server-table. – Varun Sharma May 24 '17 at 20:52

score 21 · Answer 2 · answered Mar 12 '09 at 15:45

21

How about turning on xact_abort

set xact_abort on

answered Mar 12 '09 at 15:45

dance2die

35,807
39
131
194

Sorry for not accepting this solution, but as I was using SQL Server 2005 I found the above to be the best approach - this will be noted though as I have some SQL 2000 only apps that could benefit! Thanks again! – Toran Billups Mar 12 '09 at 17:18
Thank you for the feedback. And, no, you need not worry, Toran. I wish I was the one that has suggested Josh's answer. Josh's answer surely IS a better answer. – dance2die Mar 12 '09 at 18:50

score 0 · Answer 3 · answered Mar 12 '09 at 19:24

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.

How to rollback a transaction in TSQL when string data is truncated?

3 Answers3

Linked

Related