SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 String or binary data would be truncated.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ugh3012
Yak Posting Veteran

62 Posts

Posted - 01/09/2014 :  11:42:48  Show Profile  Reply with Quote

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

India
52317 Posts

Posted - 01/10/2014 :  05:06:59  Show Profile  Reply with Quote
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
Go to Top of Page

ugh3012
Yak Posting Veteran

62 Posts

Posted - 01/10/2014 :  08:43:14  Show Profile  Reply with Quote
Oh well, I thought I would ask just in case. It took me about an hour to find it.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/12/2014 :  05:20:36  Show Profile  Reply with Quote
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

Edited by - Kristen on 01/12/2014 05:24:28
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000