March 26, 2009 at 8:24 am
I'm trying to do the most basic update statement ever and i still get this error:
Msg 8152, Level 16, State 14, Procedure CMCTR_MCTR_UPDATE, Line 63
String or binary data would be truncated.
My update statement:
UPDATE [facets451].[dbo].[CMC_MCTR_CD_TRANS]
SET [MCTR_ENTITY] = '#PCA'
WHERE MCTR_ENTITY = '#PCA' and (MCTR_TYPE = 'DDSC' OR
MCTR_TYPE = 'DFLD') and MCTR_VALUE = '0090'
But when I do a:
SELECT * FROM [facets451].[dbo].[CMC_MCTR_CD_TRANS]
WHERE MCTR_ENTITY = '#PCA' and (MCTR_TYPE = 'DDSC' OR
MCTR_TYPE = 'DFLD') and MCTR_VALUE = '0090'
I get the 2 rows that I want to update but the UDPATE statement just gives the above error listed. Any ideas?
April 21, 2009 at 1:34 am
What's the data type of the MCTR_ENTITY column?
Is this a typo?
SET [MCTR_ENTITY] = '#PCA'
WHERE MCTR_ENTITY = '#PCA'
The values are the same.
Are there any triggers on that table?
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
April 21, 2009 at 9:35 am
I had to use this:
SET ANSI_WARNINGS { ON | OFF }
to get my updates to work. This was actually a much bigger problem since I couldn't run SSIS to do bulk loading and updating of my tables. I ended setting ANSI_WARNINGS OFF before every run of SSIS or any SQL statements on the table/s (some of the tables required this annoying jumping through hoops but not all).
I'm sure its related to triggers as there are a few on each of the tables and they are VERY complicated. This is a product of another company I am in charge of managing.
-Thanks!
April 21, 2009 at 10:03 am
Turning ANSI_WARNINGS off should not be a permanent solution. ANSI warnings prevent data corruption that may be caused by inconsistent use of data types (e.g. truncation) and operations where divide-by-zero calculations are not prevented.
Make sure the operation does not cause more problems than it was supposed to solve: look at the process as a whole and determine that no data corruption occurs with ANSI_WARNINGS off. This means you will have to look at all those triggers, see what they do, and how is data quality affected by this change.
You should also consider using intermediate storage for bulk imports, and only moving data to its final destination *after* quality issues have been resolved.
ML
---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply