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

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

目前，我有一个很大的导入过程，我正试图将它封装到事务中，以便如果有什么问题的话——我可以回滚。我遇到的问题是，当trans中的TSQL崩溃时，当出现以下SQL错误时，它不会回滚

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

下面将包装这个导入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 个解决方案

#1

If your on SQL 2005 you can try:

如果你的on SQL 2005你可以试试:

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

#2

How about turning on xact_abort

打开xact_abort怎么样

set xact_abort on

#3

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.

我还要指出的是，如果您经常收到这个错误，您需要修改输入数据的列的大小，或者调整清洗过程，以便在将数据放入prod表之前对数据进行准备。在SSIS中，您还可以让deosn不满足标准大小的数据转到坏数据表并处理其余数据。

#1