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

LVL 16
JohnBPriceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
what data type is the field masterreferdate and referralrenewdate ?
0
JohnBPriceAuthor Commented:
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
0
JohnBPriceAuthor Commented:
masterrefer and remrefer are both [bit] NOT NULL
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

JohnBPriceAuthor Commented:
Hi Angel.  This seems a trivial procedure.  I have other jobs that work fine.  SQL Agent runs under a domain account with sysadmin rmembership.
0
JohnBPriceAuthor Commented:
for kicks I changed the fields to int, but still no go.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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...
0
JohnBPriceAuthor Commented:
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 ,

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CREATE TRIGGER [DlrHistoryTracker] ON dbo.dlr
FOR INSERT, UPDATE, DELETE
AS
BEGIN

     declare @TempIdentity int -- or bigint ...
     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, ' + cast(@TempIdentity as varchar(30)) + ',1) AS id  INTO #Tmp' )
          --This is needed to preserve the @@Identity which ADO uses as new field value.
     end

END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnBPriceAuthor Commented:
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.
0
JohnBPriceAuthor Commented:
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.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Glad you solved it out.
CU
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.