String or binary data would be truncated.

[Junior6202]

I'm new to Sql but I understand the basics and maybe a bit intermediate. I wrote this query to pull some information involving 3 tables dbo.NewFamNbrs$, dbo.CGIItemMaster and dbo.CGIFamilyMaster. In addition I want to perform an update statement to update column dbo.CGIItemMaster.FamilyIDX with the information of dbo.CGIFamilyMaster.IDX. I also know that truncation is indicating that I'm trying to insert data into a field not large enough. So I performed a len() function on the both columns and order by desc and they both came up with 4 as being the max. I'm out of ideas any help will be appreciated.

Error message: Msg 8152, Level 16, State 2, Procedure CGIItemMaster_UPDATE, Line 6
String or binary data would be truncated.
The statement has been terminated.

Query:

SELECT a.PART, a.Family, b.FamilyIDX, c.FamilyID, c.IDX
FROM dbo.NewFamNbrs$ a LEFT OUTER JOIN dbo.CGIFamilyMaster c ON a.Family = c.FamilyID LEFT OUTER JOIN dbo.CGIItemMaster b ON a.PART = b.PART



Results of query:

PART---------------Family---------FamilyIDX---------FamilyID--------IDX
000127233-----------TF01------------1468-------------TF01-----------506
000129880-----------TF01------------1468-------------TF01-----------506
003110--------------MET06-----------1468-------------MET06----------1915


UPDATE STATEMENT:

begin tran
update dbo.CGIItemMaster
set FamilyIDX=c.IDX
FROM dbo.NewFamNbrs$ a LEFT OUTER JOIN
CGIFamilyMaster c ON a.Family = c.FamilyID LEFT OUTER JOIN
dbo.CGIItemMaster b ON a.PART = b.PART

 

[fredericofonseca]

try select max(len(c.IDX)), max(datalength(c.IDX))

assuming the query you gave is only updating that single column it may be nvarchar vs char/varchar

I would also try and do a convert to varbinary of that field to see if there are any unprintable chars.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886