string or binary data would be truncated - WHICH FIELD?

Posted on 2012-08-20
Last Modified: 2012-09-24
THis is a highly annoying SQL error, especially since i have a huge SQL statement in a table with 30 different columns.  how do I know which field is getting kicked out without having to dissect every element of my statement?
Question by:arthurh88
    LVL 65

    Expert Comment

    by:Jim Horn
    No clue, but the way I typicall troubleshoot it is to have the table design of the source table in one window, and the table design of the destination table in another window, and compare column-by-column.

    > how do I know which field is getting kicked
    truncated just means characters to the right are not being inserted/updated.
    LVL 12

    Expert Comment

    So this is an SSIS package? Or an import wizard, which is the same anyway? Or are you using Bulk load?

    And what is the error message in it's entirity?
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    try commenting out few column names from source and destinations and run the statement.
    LVL 65

    Expert Comment

    by:Jim Horn
    <posting this just in case it's not clear>

    So if your source has a column that is a varchar(10), and your destination column that you're trying to insert/update is a varchar(5), the warning message is saying that you are at risk of losing the five characters to the right, regardless of whether it's populated or not.

    So, if your source varchar(10) contains 'abcdefg'
    What will be inserted to the destination varchar(5) is 'abcde', with the warning message.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    THis is a highly annoying SQL error, especially since i have a huge SQL statement in a table with 30 different columns.  how do I know which field is getting kicked out without having to dissect every element of my statement?
    What do you propose?  Are you suggesting that SQL Server list all the columns that could be truncated?  If that is the case, then I am afraid for a number of reasons that is not going to happen.
    LVL 18

    Expert Comment

    It would be nice if it told you the column but unfortunately, it doesn't.  You will have to do some manual investigation.  
    At least as a takeaway, you can understand the importance of properly constraining the data in the source so it stays at or below the column limit in your database.
    LVL 26

    Expert Comment

    The column(s) at risk are the ones that are longer than the target columns. How can you not know the sizes of your columns? And if you are generating values, are you defining their lengths when you generate them? If not, then there might not be a way to tell without creating a new query that generates the same values and shows their lengths.

    LVL 68

    Accepted Solution

    Yes, IT REALLY IS ANNOYING.  I too think SQL should specify the column(s) ... but, as we know, it doesn't.

    If it's an INSERT (95+% of the time), what I typically do is create the structure in tempdb, but change every string/binary column to varchar|bin(max).

    INSERT INTO tempdb.dbo.test_table
    SELECT ...
    FROM ...

    Then, after it's run, do a single query to get the MAX(LEN( of every column, and compare that to the max lengths of the original/output data.  That allows you to identify all over-length columns at the same time.

    For example, something like this:

    USE maindb

        CASE WHEN 'columnA' THEN [columnA] WHEN 'columnB' THEN [columnB] ... END AS max_length,
        CASE WHEN c.max_length = -1 THEN 'ok'
            WHEN = 'columnA' THEN [columnA]
            WHEN = 'columnB' THEN [columnB]
        END > c.max_length THEN 'TOO LONG!' ELSE 'ok' END AS check_length,
    FROM (
            MAX(LEN(columnA)) AS columnA, MAX(LEN(columnB)) AS columnB,
            MAX(LEN(columnC)) AS columnC, --, ...
        FROM tempdb.dbo.test_table
    ) AS tt
    CROSS JOIN maindb.sys.columns c ON
        c.object_id = OBJECT_ID('dbo.table_name')

    Author Closing Comment

    glad to know someone relates!!

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now