String or binary data would be truncated.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

String or binary data would be truncated.

Author

Topic

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2014-01-09 : 11:42:48

I get the below error message, but how can I find the actual line that has this problem. It does not give me the name of the field or correct line number.

Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 05:06:59

its not easy. you've to check each and every place where you try to insert a value for varchar field and make sure field has enough length to hold passed value. If its inserting from a table make sure destination tables field length is same as source table field length

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2014-01-10 : 08:43:14

Oh well, I thought I would ask just in case. It took me about an hour to find it.

Kristen
Test

22859 Posts

Posted - 2014-01-12 : 05:20:36

When that happens to me I change


INSERT INTO MyDestinationTable
(
    Col1, Col2, ...
)
SELECT Col1, Col2, ...
FROM MySourceTable

into


SELECT Col1, Col2, ...
INTO #TempTable
FROM MySourceTable

SELECT [Col1MAX] = MAX(DATALENGTH(Col1)),
       [Col2MAX] = MAX(DATALENGTH(Col2)),
       ...
FROM #TempTable

(only for the Varchar columns

) and then see which column has a width that is too wide. You can skip straight to the next step - finding which row it is


SELECT Col1, Col2, ...,
       [ErrorMessage] = CONVERT(varchar(8000), NULL)
INTO #TempTable
FROM MySourceTable

UPDATE U
SET ErrorMessage = COALESCE(ErrorMessage+'; ', '')
                 + 'Col1 too long ['
                 + CONVERT(varchar(20), DATALENGTH(Col1))
                 + ']'
WHERE DATALENGTH(Col1) > 1234   -- Need the actual Max Width of COL1 here

... Repeat for Col2, Col3, (all varchar columns) ...

-- Display any errors
SELECT *
FROM #TempTable
WHERE ErrorMessage IS NOT NULL

and if you want to insert all rows with NO errors, anyway, then you can do:


INSERT INTO MyDestinationTable
(
    Col1, Col2, ...
)
SELECT Col1, Col2, ...
FROM MySourceTable
WHERE ErrorMessage IS NOT NULL

although you need to be sure that the actual errors are reported to someone who can sort them out AND there is a means of then selectively importing those later (for example if there is a daily import of "everything" or "everything that has changed" then reporting the errors to an operators to fix, before tomorrow's run, might be good enough.

You can add any number of tests for other goofy data - illegal dates, MAX value smaller than corresponding MIN value, Names that fail a Lookup to a Contacts table, all that sort of data-validation jazz. Anything that adds an entry to the ErrorMessage column will prevent the subsequent INSERT of the junk data.

Add a WarningMessage column too, if there are any non critical errors. Import those rows anyway (provided ErrorMessage IS NULL) but include them in the error report for the operator to investigate. "Order date set to a future date!!" for example, I've had that happen when a computer's clock has been set wronly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources