Stored procedure runs when exec but doesn't run in agent job even though it's successful

Question

I've read alot of other people having similar problems but nothing I've found so far have worked. This is a SQL Server 2008 R2 and we have a agent job that runs once a day that fills a table with data. When running a new query and exec sp it works fine but when we run it as a agent job it completes as successful but doesn't actually fills any data when looking at most recent data by date. The account running sql agent is sysadmin and I've also tried running it with a SQL account with execute as user (the sql account is db_owner on the database). I've tried adding TEXTSIZE 200000 to the step. I've also ran a trace and by the looks of it the stored procedure actually run as intended

This is the SP:

    /****** Object:  StoredProcedure [dbo].[CalculateKPIs_2]    Script Date: 2019-11-20 09:06:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CalculateKPIs_2]
AS

          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;

    -- Insert statements for procedure here
          -- Calculate daily totals per api resource
  INSERT INTO API_TRAFFIC_SUMMARY_STAT 
  SELECT CONVERT(VARCHAR(10), request_datetime, 20),
       api,
             api_version,
             method, 
       resource_path,
             SUM(request_count),
             SUM(fault_count),
             MIN(min_response_time),
       SUM(request_count * avg_response_time) / SUM(request_count),
       MAX(max_response_time),
       MIN(min_service_time),
       SUM(request_count * avg_service_time) / SUM(request_count),
       MAX(max_service_time)
  FROM api_traffic_summary
  WHERE CONVERT(VARCHAR(10), request_datetime, 20) = convert(VARCHAR(10), DATEADD(DAY, -1, GETDATE()), 20)
  GROUP BY CONVERT(VARCHAR(10), request_datetime, 20), api, api_version, method, resource_path;

  -- Calculate daily totals per api resource for each application
INSERT INTO API_TRAFFIC_SUMMARY_STAT_APPLICATION 
  SELECT CONVERT(VARCHAR(10), request_datetime, 20),
                   user_id,
                   application_name,
                   api,
                   api_version,
                   method, 
                   resource_path,
                   SUM(request_count),
                   SUM(fault_count),
                   MIN(min_response_time),
                   SUM(request_count * avg_response_time) / SUM(request_count),
                   MAX(max_response_time),
                   MIN(min_service_time),
                   SUM(request_count * avg_service_time) / SUM(request_count),
                   MAX(max_service_time)
  FROM api_traffic_summary
  WHERE CONVERT(VARCHAR(10), request_datetime, 20) = convert(VARCHAR(10), DATEADD(DAY, -1, GETDATE()), 20)
  GROUP BY CONVERT(VARCHAR(10), request_datetime, 20), user_id, application_name, api, api_version, method, resource_path;

-- Calculate daily fault totals per api resource for each application
INSERT INTO API_FAULT_SUMMARY_STAT_APPLICATION 
  SELECT CONVERT(VARCHAR(10), request_datetime, 20),
                   user_id,
                   application_name,
                   api,
                   api_version,
                   method, 
                   resource_path,
                   endpoint_address,
                   error_code,
                   SUM(fault_count)
  FROM api_fault_summary
  WHERE CONVERT(VARCHAR(10), request_datetime, 20) = convert(VARCHAR(10), DATEADD(DAY, -1, GETDATE()), 20) AND user_id IS NOT NULL AND application_name IS NOT NULL
  GROUP BY CONVERT(VARCHAR(10), request_datetime, 20), user_id, application_name, api, api_version, method, resource_path, endpoint_address, error_code;

Anyone have any suggestions?

Answer 1

I've added

    IF @@ROWCOUNT = 0
    PRINT 'Something went wrong!'
ELSE PRINT 'Rows were updated...'

On every Insert statement and can now easily view the output in the agent job history as following:

    Rows were updated... 
[SQLSTATE 01000] (Message 0)  Rows were updated... 
[SQLSTATE 01000] (Message 0)  Rows were updated... 
[SQLSTATE 01000] (Message 0).  The step succeeded.

Thanks @sepupic for the help