asked on

SQL Agent job fails with "String or binary data would be truncated" but the step works fine manually.

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
BEGIN
      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
                  AND (
                        dlr.remrefer=1
                        OR datediff(Day,SentForDSMReview,GetDate()) > 15
                  );

      update dlr set masterrefer = 0 where MasterRefer = 1 and
            dbo.ReferralCandidate(dlr_identifier) = 0;
END
GO

Guy Hengel [angelIII / a3]

what data type is the field masterreferdate and referralrenewdate ?

JohnBPrice

ASKER

If I reduce the stored procedure to the following, and it still fails if it needs to set MasterRefer

CREATE PROCEDURE MarkNewMasterRefer AS
BEGIN

update dlr set masterrefer =1 where masterrefer = 0 AND (dlr.remrefer=1 );

END
GO

JohnBPrice

ASKER

masterrefer and remrefer are both [bit] NOT NULL

JohnBPrice

ASKER

Hi Angel. This seems a trivial procedure. I have other jobs that work fine. SQL Agent runs under a domain account with sysadmin rmembership.

JohnBPrice

ASKER

for kicks I changed the fields to int, but still no go.

Guy Hengel [angelIII / a3]

I asked of the data type of the fields called ....date , not the others...
It's only a guess of my side, but if they are NOT of data type datetime, this might be the explanation...

JohnBPrice

ASKER

I took the date fields out of the stored procedure, as I said above it is now simply "update dlr set masterrefer =1 where masterrefer = 0 AND dlr.remrefer=1" which is why I only gave you those two fields.

The thing is it WORKS from SQL Query Analyzer, and the date fields worked fine but are clearly irrevalent as the simplified version has the same problem.

I found it has something to do with a trigger on the table that creates and audit record, as follows. If I remove the trigger, the SQL Agent job works.

Is there something special about triggers under SQL Agent that is different that under SQL Query analyzer, remember it works just fine when I run it manually from SQL Query Analyzer.

Does App_Name() still apply under SQL Agent? Perhaps something special with the @@Identity?

Here is the trigger, the key field definitions follow:

CREATE TRIGGER [DlrHistoryTracker] ON dbo.dlr
FOR INSERT, UPDATE, DELETE
AS
BEGIN

      declare @TempIdentity varchar(10)
      declare @Rowcount int
      if app_name() <> 'ignore'
      begin
            select @TempIdentity = @@IDENTITY

            select @rowcount =(select count(*) from inserted) - (select count(*) from deleted)
            if @rowcount > 0
                  insert dlrhistory select 'Insert' 'Action',getdate() 'ChangeDate', host_name() 'HostName', app_name() 'AppName', user_name() 'UserName', inserted.* from inserted
            else if (@rowcount = 0)
                  insert dlrhistory select 'Update' 'Action',getdate() 'ChangeDate', host_name() 'HostName', app_name() 'AppName', user_name() 'UserName', inserted.* from inserted

                  else --(@Rowcount < 0 )

                        insert dlrhistory select 'Delete' 'Action',getdate() 'ChangeDate', host_name() 'HostName', app_name() 'AppName', user_name() 'UserName', deleted.* from deleted




            if @TempIdentity is not null
                  EXECUTE (N'SELECT Identity (Int, ' + @TempIdentity + ',1) AS id INTO #Tmp' )
            --This is needed to preserve the @@Identity which ADO uses as new field value.
      end

END

      [Action] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [ChangeDate] [datetime] NOT NULL ,
      [HostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [AppName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

JohnBPrice

ASKER

alas, no. BTW, I had to use

EXECUTE (N'SELECT Identity (Int, cast (' + @TempIdentity +' as varchar(30) ) ,1) AS id INTO #Tmp' )

since the Execute (N didn't like the cast in the string building part.

JohnBPrice

ASKER

Ha, found it. app_name() under SQL Agent returns "SQLAgent - TSQL JobStep (Job 0x05AC534D504D7F4FB38CF42BF9AD6D99 : Step 1)" which is 73 chars, blowing out my 50 char field.

I put prints in my trigger & set "append output to step history" and saw the long string, reset the text fields to 500, and it worked.

Thanks for your help, just discussing it with someone was a big help (I'm a one man IT shop). See you in the lounge.

Guy Hengel [angelIII / a3]

Glad you solved it out.
CU

SQL Agent job fails with &quot;String or binary data would be truncated&quot; but the step works fine manually.

SQL Agent job fails with "String or binary data would be truncated" but the step works fine manually.