?
Solved

sql transaction not catching truncating error

Posted on 2008-11-18
6
Medium Priority
?
407 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:yanci1179
  • 3
  • 3
6 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22990149
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22990167
Are you also sure the error is not coming from your @ErrMsg stmt?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22990214
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

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

0
 
LVL 9

Expert Comment

by:Ernariash
ID: 22990331
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
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22990391
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question