none
String or binary data would be truncated. The statement has been terminated.

    Question

  • Msg 8152, Level 16, State 14, Procedure CycleTimeSpudtoTD, Line 23
    String or binary data would be truncated.
    The statement has been terminated.

     

    Destination table:

    [dbo].[WDPWfFactTable](
        [EndDate] [datetime] NULL,
        [StartDate] [datetime] NULL,
        [ValueInDays] [decimal](12,2) NULL,
        [ValueInMinutes] [int] NULL,
        [DateTime] [datetime] NULL,
        [WellID] [int] NULL,
        [WdpMeasureID] [int] NULL,
        [AssetID] [int] NULL,
        [UnitofMeasure] [varchar] (50) NULL,
        [Month] [varchar](50) NULL

    )

     

    Inserting data in format of :

     

    EndDate                                  StartDate                   ValueInDays    ValueInMinutes    DateTime             WellID    WdpMeasureID    AssetID   
    2011-07-19 11:30:07.633    2011-07-19 11:03:19.120    0.00            27            2011-04-24 13:35:35.200    12       2                        0                  


    2011-07-15 04:11:55.740    2011-07-15 04:06:21.537    0.00             5             2011-04-24 13:35:35.200    17       3                        0                  

    UnitofMeasure    Month

    Day                 08/11

    Day                08/11

     

    I have procedure which inserts the data into the destination table. when I in the procedure, I can see some data in the destination table but query completed with this error

     

    Msg 8152, Level 16, State 14, Procedure CycleTimeSpudtoTD, Line 23
    String or binary data would be truncated.
    The statement has been terminated.

     

    So, It might be the Issue with the data type and the  size.  (may be decimal datatype? ). I couldn't guess exactly in which column the error is from.

    (Select convert(decimal(12,2), DATEDIFF(HOUR,t1.[CompletedDate],t2.[CompletedDate])/24.00)) <-- This is what I am using in procedure for decimal datatype.

    Please help me to solve this issue with your suggestions.

    Thanks,

    TOM.

     



    Tuesday, August 30, 2011 4:27 PM

Answers

  • One more column is Month column which I haven't noticed at first. Check it also and also verify if you have triggers on the target table.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by MkrTOM Tuesday, August 30, 2011 8:53 PM
    Tuesday, August 30, 2011 4:54 PM
    Moderator

All replies

  • This error relates to character field. In your table it looks like the only char field is UnitOfMeasure. So, check your original table and that field if it exceeds 50 chars.

    See also

    How to find what column caused the String or binary data would be truncated message


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by KEROBIN Tuesday, August 30, 2011 4:51 PM
    Tuesday, August 30, 2011 4:32 PM
    Moderator
  • Thanks for your reply.

     

    The "UnitofMeasure" column has the data type in the source table is "Varchar(50) " and the destination table with the same datatype "Varchar(50)"

     

    TOM.

    Tuesday, August 30, 2011 4:51 PM
  • One more column is Month column which I haven't noticed at first. Check it also and also verify if you have triggers on the target table.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by MkrTOM Tuesday, August 30, 2011 8:53 PM
    Tuesday, August 30, 2011 4:54 PM
    Moderator
  • Thanks, Its was an issue with Month column.

     

    TOM

    Tuesday, August 30, 2011 8:53 PM