How to find out error record and column which is causing error during data load into a table ????????

  • Hi Experts,

    Need help in nailing down row causing the error and the column which is causing the "String or binary data would be truncated" during huge data loads into datawarehouse system.

    Requirement:

    1. First of, I want to know which row is causing the error "String or binary data would be truncated."
    2. Secondly, Which column of that row is causing the error "String or binary data would be truncated."

    How to find out error row and column which is causing the error in that row???

    Here, for the sake of demo i am using only 4 columns in the table but my production table can have like 40-50 columns of various data types.

    SQL Version : SQL 2016 Enterprise Edition

    Test data
    =============

    create table dummy_tbl
    (c1 int,
    c2 varchar(10),
    c3 varchar(10),
    c4 datetime default getdate()
    )

    create proc usp_p1
    as
    begin

            insert into dummy_tbl(c1,c2,c3,c4)
            select 101,'promocode1','descadasd',getdate()
            union all
            select 102,'promocode2','descadasd',getdate()
            union all
            select 103,'promocode3','descaddffjhsdfjhskjdfhkjsdfsdfasd',getdate()  --- errornous row
            union all
            select 104,'promocode4','descadasd',getdate()

    end
    go

    EXEC usp_p1
    go

    /*
    Msg 8152, Level 16, State 14, Procedure usp_p1, Line 6
    String or binary data would be truncated.
    The statement has been terminated.

    */

    Thanks in advance.

    Sam

  • That's kind of like asking me to call balls and strikes on pitches already made to the plate that neither of us has seen.   You've said nothing about HOW that data is loaded into the DW, and nothing about what process is experiencing this message.   And there's no video of those pitches...   You'll need to provide the detail, not just the concepts...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Please forget about the datawarehouse load. Assume , it tsql table and above INSERT , how can I figured out what column in the table causing the String Truncation error ??

  • vsamantha35 - Thursday, April 6, 2017 1:10 PM

    Please forget about the datawarehouse load. Assume , it tsql table and above INSERT , how can I figured out what column in the table causing the String Truncation error ??

    You've completely missed the point.   How am I supposed to answer a question for which I have no useful details?   I'm supposed to guess based on some totally unrealistic query???   Really?   If all I had was the insert and the single table create statement the answer would NOT require my help.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • All that the stored proc is doing is below.

    create proc <procname>
    as
    begin  

    insert into staging_tbl
    select c1,c2,c3,c4.......c50 from <LinkedServer>.<db1>.dbo.table

    end

  • Does any trace / extended evet session help ??????

  • vsamantha35 - Thursday, April 6, 2017 2:00 PM

    All that the stored proc is doing is below.

    create proc <procname>
    as
    begin  

    insert into staging_tbl
    select c1,c2,c3,c4.......c50 from <LinkedServer>.<db1>.dbo.table

    end

    And now the insert your stored procedure is doing is no longer one that just inserts hard-coded data...  what will you change next?   When you have an insert based on some other table or tables, then you'll have to look at each and every one of those tables and compare the data types so that you can see where any differences are between the fields being selected and the fields into which they're being inserted.   Differences in length for character based fields is the most likely culprit.   Then, once you narrow down the candidate fields, you can look at the max of the len for each one in the source table or tables and see what the actual maximum length is in each case.  Then just find any record where the length of that field is equal to that max length.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Thank you,.

    Any thoughts on how to find out the culprit row?

  • Read my last post again.   It DOES provide a recommended way.   You DO have to do some of this work yourself, you know...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • your data type sized obviously don't match...since it's a linked server, you can properly interrogate their sizes.

    if both sides are SQL server, a simple EXCEPT operator should work:
    SELECT * into [name] ,
    [column_id],
    [system_type_id] ,
    [user_type_id] ,
    [max_length] ,
    [precision]  ,
    [scale]  FROM sys.columns WHERE name =staging_tbl
    EXCEPT
    SELECT * into [name] ,
    [column_id],
    [system_type_id] ,
    [user_type_id] ,
    [max_length] ,
    [precision]  ,
    [scale]  FROM <LinkedServer>.<db1>.sys.columns WHERE name ='table'

    if it's not SQL:, just insert into a temp table and compare the tempdb table's schema to the staging table
    SELECT * INTO #TempCompareSchema FROM <LinkedServer>.<db1>.dbo.table WHERE 1=2

    and compare tempdb the same way
    ...FROM tempdb.sys.columns WHERE object_id = object_id('#TempCompareSchema')

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply