Why does my SQL agent job keep failing?

Question

I have a SQL agent job that just runs a basic query on a schedule. It updates info based on the query shown.

USE DB
DECLARE @startDate AS DATETIME
DECLARE @endDate AS DATETIME
DECLARE @rcount AS VARCHAR(10)
SET @startDate = CAST(CONVERT(VARCHAR(10),GETDATE(),112) AS DATETIME) 
SET @endDate = CAST(CONVERT(VARCHAR(10),GETDATE(),112) AS DATETIME) 
--*********************************************************
-- *Run Query
--*********************************************************
UPDATE Table1
SET Table1.field1 = 'ZPR' +  Left(Table1.field1,6),
    Table1.field2 = '0',
    Table1.field3 = '0' 
WHERE  Table1.GUID IN (SELECT GUID FROM Table1 T2 
    WHERE T2.Date >= @startDate 
        AND T2.Date <= @endDate 
        AND   T2.complete = 0)
    AND Table1.co IN (SELECT co FROM VIEW('ZERO')) 
SELECT @rcount = CAST(@@ROWCOUNT AS VARCHAR(10)) + ' ' + 'row(s) affected by UPDATE';
--*********************************************************
--* Print Results
--*********************************************************
DECLARE @eSubject varchar(250)
DECLARE @emailTo varchar(250)
SET @eSubject = 'Number of rows updated'   
SET @emailTo = '[email protected]' 
EXEC msdb.dbo.sp_send_dbmail @recipients=@emailTo,
    @subject = @eSubject,
    @body = @rcount,
    @body_format = 'HTML';

The job runs perfectly when there isn't any updates done but fails whenever there is. The user that this job is ran under has read and write access. The error that I get is "The string or the binary data would be truncated [sql220001] [error 8152]". I'm not sure why it keeps failing and any help would be amazing!

******************UPDATE**********

I'm going crazy here. It fails as a scheduled job but runs perfectly under any other user as a straight query. The fields are as such:

Table1.field1 = PK length of 10 Table1.field2 = bit length of 1 Table1.field3 = bit length of 1

no matter what I try or do the SQL job fails with the same error but it is making me nuts that I can run the query by itself and it works flawlessly.

Answer 1

Try changing @rcount to varchar(50). I think that's the issue.

EDIT:

Since my first suggestion didn't solve your issue, look for triggers on your table. It could also give you that error if a trigger is being run that is trying to put, say, 50 characters into a varchar(25) field.

You can get around this error in the future by, before any set command (or select @var = [insert data here]), do a LEFT([insert data here], #) (# being the maximum length of that field).

Answer 2

Maybe there are some triggers set on the Table1 causing the issue? If there are you should check the code of those as well.