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

  • What do these Copy Only Backup options mean?
  • mysql_real_escape_string alternative for SQL Server
  • SQLBulkCopy Row Count When Complete
  • SQL Server - Logical Question
  • finding max possible date in ms sql server 2005+
  • Rewriting a LINQ Expression query to enable caching SQL Execution Plan
  • 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
    

  • Entity Framework 6 and SQL Server Sequences
  • Write advanced SQL Select
  • How to compare different values in sql server
  • protect stored procedure by deny view definition
  • SQL left join with multiple rows into one row
  • sql server: need to escape [?
  • 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.