• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10712
  • Last Modified:

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

0
JohnBPrice
Asked:
JohnBPrice
  • 7
  • 4
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now