We help IT Professionals succeed at work.

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

JohnBPrice
JohnBPrice asked
on
Medium Priority
12,740 Views
Last Modified: 2008-05-05
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

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
what data type is the field masterreferdate and referralrenewdate ?

Author

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

Author

Commented:
masterrefer and remrefer are both [bit] NOT NULL

Author

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.

Author

Commented:
for kicks I changed the fields to int, but still no go.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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...

Author

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 ,

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.

Author

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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Glad you solved it out.
CU
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.