时间戳

首页 > 解决方案 > 存储过程在执行时运行，但即使成功也不会在代理作业中运行

sql-server - 存储过程在执行时运行，但即使成功也不会在代理作业中运行

问题描述

我读过很多其他有类似问题的人，但到目前为止我没有发现任何工作。这是一个 SQL Server 2008 R2，我们有一个每天运行一次的代理作业，它用数据填充表。运行新查询和 exec sp 时它工作正常，但是当我们将它作为代理作业运行时，它会成功完成，但在按日期查看最新数据时实际上并没有填充任何数据。运行 sql 代理的帐户是 sysadmin，我也尝试使用以用户身份执行的 SQL 帐户运行它（sql 帐户是数据库上的 db_owner）。我尝试将 TEXTSIZE 200000 添加到步骤中。我还运行了一个跟踪，从外观上看，存储过程实际上按预期运行

这是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;

有人有什么建议吗？

标签： sql-serversql-server-2008stored-proceduressql-agent-job

解决方案

我已经添加

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

在每个 Insert 语句上，现在可以轻松地查看代理作业历史记录中的输出，如下所示：

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

感谢@sepupic 的帮助

推荐阅读