destination overflowed problem | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

destination overflowed problem

I’m getting the error listed below. Column 11 of a text file contains the value ‘05360’ which I’m trying to move to the column rebate_rate, which is a decimal with a precision of 5, scale of 3. How can it overflow? It should be a string of 5 to five digits.<br /><br />Den<br /><br />Error during Transformation ‘DTSTransformation__8’ for Row number 1. Errors encountered so far in this task: 1. <br /><br />Error Source: Microsoft Data Transformation Services (DTS) Data Pump<br />Error Description:TransformCopy ‘DTSTransformation__8’ conversion error: Destination overflowed on column pair 1 (source column ‘Col011’ (DBTYPE_STR), destination column ‘Rebate_rate’ (DBTYPE_NUMERIC)).<br />Error Help File<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />qldts80.hlp<br />Error Help Context ID:30501<br />B|0018|00206R102||000053700||000166470000||07052006|261860009|05360|| |C|
"Precision" is the total number of digits, "scale" is the number of those digits that are decimals. In other words, DECIMAL(5,3) won’t even accept a value of 100. The largest value that is accepted without overflow is 99.999499999999999999999999999999999999 – 99.9995 would be rounded to 100, which would cause the overflow. declare @t decimal(5,3)
set @t = 99.999499999999999999999999999999999999
select @t ——-

Thanks for the suggestion – it worked. SQL Server is looking for the decimal point in the text file, ie. 05.360, not 05630. I can fix that. Den