字符串数据被截断时如何回滚TSQL中的事务?

沃梦达教程数据库问题

2021-01-01 4

How to rollback a transaction in TSQL when string data is truncated?(字符串数据被截断时如何回滚TSQL中的事务?)

本文介绍了字符串数据被截断时如何回滚TSQL中的事务?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

目前我有一个很大的导入过程，我试图将它包装在一个事务中，所以如果有任何问题 - 我可以回滚.我遇到的问题是trans里面的TSQL炸了，出现下面的SQL错误时不会回滚

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.

下面包装了这个导入的TSQL

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

推荐答案

如果你使用的是 SQL 2005 你可以试试:

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