String or binary data would be truncated Error message

[PreacherUK]

Hi Guys

Below is an update query. It keeps returning the following error:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

I would assume this refers to my destination fields being to narrow for the data I'm inserting. However just to try I increased the width of all the destination fields but still continued to receive the error.
Apologies for the poor formatting, the submit form here mangles the code.

UPDATE dbo.[tblMonthEndEstimate]
SET dbo.[tblMonthEndEstimate].[ClientType] = dbo.[tblLending Accounts].[TaxGroup],
dbo.[tblMonthEndEstimate].[ClientDom] = dbo.[tblLending Accounts].[ClientDomocile],
dbo.[tblMonthEndEstimate].[AccountName] = dbo.[tblLending Accounts].[Lending Account Name]
FROM dbo.[tblLending Accounts] RIGHT OUTER JOIN
dbo.[tblMonthEndEstimate] ON dbo.[tblLending Accounts].[acct-num] = dbo.[tblMonthEndEstimate].GSPAccountRef

 

[PreacherUK]

Just an update, I had some NULLs in the [tblLending Accounts].[Lending Account Name] field, added a NO NULLs criteria to my update query and it works fine now.

 

[vongrunt]

What are exact data types and lengths of all columns in query? And do you have some kind of background process (e.g. trigger) on target table?

 

[PreacherUK]

the data types that I was updating are all varchar and the lengths of the fields in the destination table matched the source tables.


The query now looks like this:

UPDATE dbo.[tblMonthEndEstimate]
SET dbo.[tblMonthEndEstimate].[AccountName] = dbo.[tblLending Accounts].[Lending Account Name]
FROM dbo.[tblLending Accounts] RIGHT OUTER JOIN
dbo.tblMonthEndEstimate ON dbo.[tblLending Accounts].[ACCT-NUM] = dbo.tblMonthEndEstimate.GSPAccountRef
WHERE (LEN(dbo.[tblLending Accounts].[Lending Account Name]) IS NOT NULL)

 

[jelf]

Do you have any triggers on your table?

 

[PreacherUK]

No, no triggers at all

 

[jelf]

Can you post the schema for the tables?

 

[Kris11]

Is this update happening through a storedProcedure? If so do you have any other SELECT with an aggreate function that might have a NULL value in it? Since this is only a warning, if you want you can hide it by using

SET ANSI_WARNINGS OFF

-Kris