String or binary data would be truncated

  • I'm getting this error, and I know how to fix it, but I don't understand why I'm getting it.

    I create this temporary table

    CREATE TABLE #QualifiedCases(

    CaseIDINT,

    CaseNumberVARCHAR(50),

    CaseCategoryKeyVARCHAR(10),

    CaseUTypeIDINT,

    CaseTypeCodeVARCHAR(10),

    CaseTitleVARCHAR(100),

    NodeIDINT,

    CurrentCaseStatusRecordNumberINT,

    CurrentCaseStatusIDINT,

    CaseStatusDateDATETIME,

    CaseStatusDescVARCHAR(100))

    I then try to populate it

    INSERT INTO #QualifiedCases (

    CaseID,

    CaseNumber,

    CaseCategoryKey,

    CaseUTypeID,

    CaseTitle,

    NodeID,

    CurrentCaseStatusRecordNumber,

    CurrentCaseStatusID,

    CaseStatusDate,

    CaseTypeCode)

    SELECT

    cch.CaseIDAS CaseID,

    cah.CaseNbrAS CaseNumber,

    cch.CaseCategoryKey AS CaseCategoryKey,

    cch.CaseUTypeID AS CaseUTypeID,

    Left(cch.Style,100)AS CaseTitle,

    cah.NodeID AS NodeID,

    csh.StatusIDAS CurrentCaseStatusRecordNumber,

    cch.CaseStatClkCdIDAS CurrentCaseStatusID,

    csh.DtCaseStatusAS CaseStatusDate,

    uc.CodeAS CaseTypeCode

    FROM

    ClkCaseHdr AS cch

    INNER JOIN CaseAssignHist AS cah

    ON cch.CaseID = cah.CaseID

    AND cah.DtXferOut IS NUL

    INNER Join uClkCaseStatus AS uccs

    ON uccs.ClerkCaseStatusID = cch.CaseStatClkCdID

    LEFT OUTER JOIN CaseStatusHistAS csh

    ON cch.StatusIDCur = csh.StatusID

    AND cch.CaseID = csh.CaseID

    INNER JOIN uCode AS uc

    ON cch.CaseUTypeID = uc.CodeID

    WHERE uccs.FlagActive = 1

    The reason I get the truncation message is because the longest value stored in uc.Code is 14 characters in length, not the 10 characters defined in the temp table. When I expand the temp table column length to 14, the code works fine. It obviously needs to be expanded to 20 characters, which is the length of the uc.Code column.

    So what's the problem? None of the values that end up in CaseTypeCode is longer than 10 positions. So it's failing because it's possible that truncation would occur, but truncation doesn't occur.

    As near as I can remember, uc.Code has always been 20 characters in length. It doesn't look like any of the greater than 10 character values have been added recently. So why has the stored procedure started throwing this error at all, since it's just anticipating problems it doesn't encounter, and why now?

    Thanks,

    Mattie

  • You are getting that error because some value in one of your fields is larger than the field size in the temp table. It is one of the most frustrating errors from sql ever. Obviously the engine knows which field but it doesn't report which field.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 2 posts - 1 through 1 (of 1 total)

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