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

yanci1179Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HoggZillaCommented:
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.
 
 
0
HoggZillaCommented:
Are you also sure the error is not coming from your @ErrMsg stmt?
0
HoggZillaCommented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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

0
ErnariashCommented:
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

0
ErnariashCommented:
There is nothing wrong with your Try ..Catch
The ERROR_SEVERITY is 16 for your error: String or binary data would be truncated 
Then The begin catch does catch this error: only errors that have a severity of 10 or lower that are considered warnings or informational messages are not handled by TRY&CATCH blocks or errors with severity of 20 or higher that cause the Database Engine to close the connection.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.