SQL Agent job fails with "String or binary data would be truncated" but the step works fine manually.
Posted on 2006-04-04
I have a SQL Agent job whose only step is "exec MarkNewMasterRefer" where MarkNewMasterRefer is a stored procedure. Basically it sets some flags and fields.
If SQL Agent runs it, and it has any flags to set, it fails with "Executed as user: databanque\sqlserver. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed."
If I run "exec MarkNewMasterRefer" from SQL Query Analyzer, it works just find.
If I run it from SQL Agent and it doesn't have any flags to set, it succeeds.
Since it ALWAYS works from SQL Query Analyzer, I am at a loss as to how to find where & why "String or binary data would be truncated" could be occurring. There is nothing in SQLAgent.out nor Errorlog
My stored procedure is
CREATE PROCEDURE MarkNewMasterRefer AS
declare @NewDate datetime;
select @NewDate = getdate();
update dlr set masterrefer =1, masterreferdate = @NewDate, referralrenewdate = @NewDate , SentForDSMReview = null
where masterrefer = 0
AND dbo.ReferralCandidate(dlr_identifier ) = 1
OR datediff(Day,SentForDSMReview,GetDate()) > 15
update dlr set masterrefer = 0 where MasterRefer = 1 and
dbo.ReferralCandidate(dlr_identifier) = 0;