February 7, 2012 at 8:31 am
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
February 7, 2012 at 8:59 am
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