asked on

sql transaction not catching truncating error

I have a transaction that is doing an insert. when I do the insert alone i get the following error:

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.

The error is correct, one of the columns contains more data that the column size of the destination. However, the begin catch does not catch this error. The data gets truncated and the data is still inserted. I do not get a print out of the error. How can I modify the transaction so it will catch this error?

BEGIN TRY
  insert into table1 (col1, col2)
  select col1, col2 from table2
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0
 
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()
 
  RAISERROR(@ErrMsg, @ErrSeverity, 1)
print @errmsg
END CATCH

Open in new window

Steve Hogg

Why don't you limit the inserted length by using a substring.
example: select substring(col1,1,30) ....where 30 is the maximum length of the value you can insert.

Steve Hogg

Are you also sure the error is not coming from your @ErrMsg stmt?

Steve Hogg

Yep, I'm pretty sure the Error you receive is exactly what you programmed for. It is working as expected. Comment out your Error statements and it will return nothing. That means it caught the error and did not tell you.

Ernariash

Please you are RAISERROR(@ErrMsg, @ErrSeverity, 1) inside your
BEGIN CATCH, you never get to
print @errmsg

Ernariash

Try: comment the RAISERROR(@ErrMsg, @ErrSeverity, 1) line

BEGIN TRY
  insert into table1 (col1, col2)
  select col1, col2 from table2
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0
 
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()
 
 ----- RAISERROR(@ErrMsg, @ErrSeverity, 1)
print @errmsg
END CATCH

Open in new window

ASKER CERTIFIED SOLUTION

Ernariash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial