Error message from query execution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Error message from query execution

Author

Topic

robert693
Starting Member

42 Posts

Posted - 2009-07-27 : 14:38:41

I am trying to run a query to update a table in my database. This is the query:
UPDATE [External] SET EMail = ('RFAX: '+LEFT(First_Name,1)+', '+LEFT(Last_Name,15)+', '+Suffix+'@/FN='+CASE WHEN LEFT(Fax,1) <> '1' THEN '1' ELSE '' END + Replace(Replace(Replace(Replace(Fax,'(',''),')',''),'-',''),'/',''));

I tried to run this and got the error:

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

The Email field in the External table is a varchar(40) data type. I changed the Last_Name data that is being put into the Email field to a low as 1 character and I still got the error message. Is this error due to the length of the data being put into the Eamil field or the data types?

Thank you,

Robert

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-27 : 15:00:34

it is because you're trying to put more than 40 charaters into the email field

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-27 : 15:04:06

this will identify the offending records:



SELECT	*
FROM	[External]
WHERE	LEN
	('RFAX: '+LEFT(First_Name,1)+', '+LEFT(Last_Name,15)+', '+Suffix+'@/FN='+CASE WHEN LEFT(Fax,1) <> '1' THEN '1' ELSE '' END +
	Replace(Replace(Replace(Replace(Fax,'(',''),')',''),'-',''),'/',''))
)      > 40;

robert693
Starting Member

42 Posts

Posted - 2009-07-27 : 15:44:31

I dumped the table into Access and ran the query in there. It worked fine and ald checked the length of the data in the email field after I ran it and there were no records that were more than 40 characters. Some were exactly 40 but none more than 40. I also changed the Last_Name field to take only 10 and then only 5 characters and I still got the error. Is there another reason the error would come up?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources