SQL Agent Job仅执行Delete未执行Insert：存储过程问题求助

我编写了一个存储过程，通过SQL Agent Job调度执行。该Job执行耗时10秒，仅完成[Fact].[CommissionLiveData]表的删除操作，未插入任何数据；但手动执行该存储过程时，可正常删除并插入2800行数据。存储过程语法正确，Job执行无报错，似乎仅执行删除后就停止了。以下是原代码及更新后的代码，恳请帮忙排查问题所在：

原代码 #

CREATE OR ALTER PROCEDURE Dim.Test
AS

BEGIN TRANSACTION Trans1

    BEGIN TRY
        DELETE FROM [Fact].[CommissionLiveData];
    END TRY
    BEGIN CATCH
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;

        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH

    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION;

    BEGIN TRY
        INSERT INTO [Fact].[CommissionLiveData] (ProjectSID,
Project,
ProjectID_Char,
ProjectHyperlink,
OpportunityHyperlink,
OpportunitySID,
Opportunity,
MasterCustomerSID,
MasterCustomer,
OwnerSID,
Owner,
ProjectStartdateSID,
ProjectStartDate,
TeamSID,
Team,
CommercialTeam,
PortfolioSID,
Portfolio,
OpportunityTypeSID,
OpportunityType,
ActualCloseDateSID,
ActualCloseDate,
RevenueSplit,
durationmonths,
RevenueDateSID,
RevenueDate,
RevenueQuarter,
RevenueYear,
MonthTCV,
OpportunityTCV,
OpportunityACV,
NewCustomerFlag,
ClosedIn2023Flag,
ManagedServicesFlag,
RevenueRecognitionFlag,
TCVTargetFlag,
RevenueTargetFlag,
NNPTargetFlag,
ACVTargetFlag,
Level5Flag,
ActiveEmployeeFlag,
Currency,
RevAdj,
GP,
GPPct,
BaseRate,
GPBooster,
BaseCommissionRate,
DealSizeBooster,
DealTypeBooster,
DealDurationBooster,
TA_ACVBooster,
TA_TCVBooster,
TA_NNPBooster,
TA_RevenueBooster,
TABooster,
MSBooster,
NewCustomerBooster,
PublicCloudBooster,
Commission2023,
Commission2022,
Level5Deductions
)
SELECT  
c.[ProjectSID]  as  ProjectSID,
c.[Project] as  Project,
c.[ProjectID_Char]  as  ProjectID_Char,
c.[ProjectHyperlink]    as  ProjectHyperlink,
c.[OpportunityHyperlink]    as  OpportunityHyperlink,
c.[OpportunitySID]  as  OpportunitySID,
c.[Opportunity] as  Opportunity,
c.[MasterCustomerSID]   as  MasterCustomerSID,
c.[MasterCustomer]  as  MasterCustomer,
c.[OwnerSID]    as  OwnerSID,
c.[Owner]   as  Owner,
c.[ProjectStartdateSID] as  ProjectStartdateSID,
c.[ProjectStartDate]    as  ProjectStartDate,
c.[TeamSID] as  TeamSID,
c.[Team]    as  Team,
c.[CommercialTeam]  as  CommercialTeam,
c.[PortfolioSID]    as  PortfolioSID,
c.[Portfolio]   as  Portfolio,
c.[OpportunityTypeSID]  as  OpportunityTypeSID,
c.[OpportunityType] as  OpportunityType,
c.[ActualCloseDateSID]  as  ActualCloseDateSID,
c.[ActualCloseDate] as  ActualCloseDate,
c.[RevenueSplit]    as  RevenueSplit,
c.[durationmonths]  as  durationmonths,
c.[RevenueDateSID]  as  RevenueDateSID,
c.[RevenueDate] as  RevenueDate,
c.[RevenueQuarter]  as  RevenueQuarter,
c.[RevenueYear] as  RevenueYear,
c.[MonthTCV]    as  MonthTCV,
c.[OpportunityTCV]  as  OpportunityTCV,
c.[OpportunityACV]  as  OpportunityACV,
c.[NewCustomerFlag] as  NewCustomerFlag,
c.[ClosedIn2023Flag]    as  ClosedIn2023Flag,
c.[ManagedServicesFlag] as  ManagedServicesFlag,
c.[RevenueRecognitionFlag]  as  RevenueRecognitionFlag,
c.[TCVTargetFlag]   as  TCVTargetFlag,
c.[RevenueTargetFlag]   as  RevenueTargetFlag,
c.[NNPTargetFlag]   as  NNPTargetFlag,
c.[ACVTargetFlag]   as  ACVTargetFlag,
c.[Level5Flag]  as  Level5Flag,
c.[ActiveEmployeeFlag]  as  ActiveEmployeeFlag,
c.[Currency]    as  Currency,
c.[RevAdj]  as  RevAdj,
c.[GP]  as  GP,
c.[GPPct]   as  GPPct,
c.[BaseRate]    as  BaseRate,
c.[GPBooster]   as  GPBooster,
c.[BaseCommissionRate]  as  BaseCommissionRate,
c.[DealSizeBooster] as  DealSizeBooster,
c.[DealTypeBooster] as  DealTypeBooster,
c.[DealDurationBooster] as  DealDurationBooster,
c.[TA_ACVBooster]   as  TA_ACVBooster,
c.[TA_TCVBooster]   as  TA_TCVBooster,
c.[TA_NNPBooster]   as  TA_NNPBooster,                    
c.[TA_RevenueBooster]   as  TA_RevenueBooster,
c.[TABooster]   as  TABooster,
c.[MSBooster]   as  MSBooster,
c.[NewCustomerBooster]  as  NewCustomerBooster,
c.[PublicCloudBooster]  as  PublicCloudBooster,
c.[Commission2023]  as  Commission2023,
c.[Commission2022]  as  Commission2022,
c.[Level5Deductions]    as  Level5Deductions
FROM 
(SELECT CASE WHEN MONTH(GETDATE()) IN (2,5,8,11) AND (DATEPART(DAY, GETDATE())) > 8 THEN 1 
                                           WHEN MONTH(GETDATE()) IN (3,6,9,12) THEN 1
                                           ELSE 0 END CRF,* FROM [Presentation].[vDimCommissionData]) c
WHERE ( c.RevenueDate >= DATEADD( QUARTER, DATEDIFF( QUARTER, 0, GETDATE()), 0) AND CRF = 1)  
OR (c.RevenueDate >= DATEADD( QUARTER, DATEDIFF( QUARTER, 0, GETDATE()) - 1, 0) AND CRF = 0);
 --If revenue date is greater than or equal to the 9th day in the second month of the quarter,   We will display current quarters information and onwards else Previous quarter information and onwards 
END TRY


BEGIN CATCH
      SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

更新后代码 #

CREATE OR ALTER PROCEDURE Dim.Test
AS
    BEGIN TRANSACTION Trans1
        BEGIN TRY
            DELETE FROM [Fact].[CommissionLiveData];

            INSERT INTO [Fact].[CommissionLiveData] (ProjectSID,
Project,
ProjectID_Char,
ProjectHyperlink,
OpportunityHyperlink,
OpportunitySID,
Opportunity,
MasterCustomerSID,
MasterCustomer,
OwnerSID,
Owner,
ProjectStartdateSID,
ProjectStartDate,
TeamSID,
Team,
CommercialTeam,
PortfolioSID,
Portfolio,
OpportunityTypeSID,
OpportunityType,
ActualCloseDateSID,
ActualCloseDate,
RevenueSplit,
durationmonths,
RevenueDateSID,
RevenueDate,
RevenueQuarter,
RevenueYear,
MonthTCV,
OpportunityTCV,
OpportunityACV,
NewCustomerFlag,
ClosedIn2023Flag,
ManagedServicesFlag,
RevenueRecognitionFlag,
TCVTargetFlag,
RevenueTargetFlag,
NNPTargetFlag,
ACVTargetFlag,
Level5Flag,
ActiveEmployeeFlag,
Currency,
RevAdj,
GP,
GPPct,
BaseRate,
GPBooster,
BaseCommissionRate,
DealSizeBooster,
DealTypeBooster,
DealDurationBooster,
TA_ACVBooster,
TA_TCVBooster,
TA_NNPBooster,
TA_RevenueBooster,
TABooster,
MSBooster,
NewCustomerBooster,
PublicCloudBooster,
Commission2023,
Commission2022,
Level5Deductions
)
SELECT  
c.[ProjectSID]  as  ProjectSID,
c.[Project] as  Project,
c.[ProjectID_Char]  as  ProjectID_Char,
c.[ProjectHyperlink]    as  ProjectHyperlink,
c.[OpportunityHyperlink]    as  OpportunityHyperlink,
c.[OpportunitySID]  as  OpportunitySID,
c.[Opportunity] as  Opportunity,
c.[MasterCustomerSID]   as  MasterCustomerSID,
c.[MasterCustomer]  as  MasterCustomer,
c.[OwnerSID]    as  OwnerSID,
c.[Owner]   as  Owner,
c.[ProjectStartdateSID] as  ProjectStartdateSID,
c.[ProjectStartDate]    as  ProjectStartDate,
c.[TeamSID] as  TeamSID,
c.[Team]    as  Team,
c.[CommercialTeam]  as  CommercialTeam,
c.[PortfolioSID]    as  PortfolioSID,
c.[Portfolio]   as  Portfolio,
c.[OpportunityTypeSID]  as  OpportunityTypeSID,
c.[OpportunityType] as  OpportunityType,
c.[ActualCloseDateSID]  as  ActualCloseDateSID,
c.[ActualCloseDate] as  ActualCloseDate,
c.[RevenueSplit]    as  RevenueSplit,
c.[durationmonths]  as  durationmonths,
c.[RevenueDateSID]  as  RevenueDateSID,
c.[RevenueDate] as  RevenueDate,
c.[RevenueQuarter]  as  RevenueQuarter,
c.[RevenueYear] as  RevenueYear,
c.[MonthTCV]    as  MonthTCV,
c.[OpportunityTCV]  as  OpportunityTCV,
c.[OpportunityACV]  as  OpportunityACV,
c.[NewCustomerFlag] as  NewCustomerFlag,
c.[ClosedIn2023Flag]    as  ClosedIn2023Flag,
c.[ManagedServicesFlag] as  ManagedServicesFlag,
c.[RevenueRecognitionFlag]  as  RevenueRecognitionFlag,
c.[TCVTargetFlag]   as  TCVTargetFlag,
c.[RevenueTargetFlag]   as  RevenueTargetFlag,
c.[NNPTargetFlag]   as  NNPTargetFlag,
c.[ACVTargetFlag]   as  ACVTargetFlag,
c.[Level5Flag]  as  Level5Flag,
c.[ActiveEmployeeFlag]  as  ActiveEmployeeFlag,
c.[Currency]    as  Currency,
c.[RevAdj]  as  RevAdj,
c.[GP]  as  GP,
c.[GPPct]   as  GPPct,
c.[BaseRate]    as  BaseRate,
c.[GPBooster]   as  GPBooster,
c.[BaseCommissionRate]  as  BaseCommissionRate,
c.[DealSizeBooster] as  DealSizeBooster,
c.[DealTypeBooster] as  DealTypeBooster,
c.[DealDurationBooster] as  DealDurationBooster,
c.[TA_ACVBooster]   as  TA_ACVBooster,
c.[TA_TCVBooster]   as  TA_TCVBooster,
c.[TA_NNPBooster]   as  TA_NNPBooster,                    
c.[TA_RevenueBooster]   as  TA_RevenueBooster,
c.[TABooster]   as  TABooster,
c.[MSBooster]   as  MSBooster,
c.[NewCustomerBooster]  as  NewCustomerBooster,
c.[PublicCloudBooster]  as  PublicCloudBooster,
c.[Commission2023]  as  Commission2023,
c.[Commission2022]  as  Commission2022,
c.[Level5Deductions]    as  Level5Deductions
FROM 
(SELECT CASE WHEN MONTH(GETDATE()) IN (2,5,8,11) AND (DATEPART(DAY, GETDATE())) > 8 THEN 1 
                                           WHEN MONTH(GETDATE()) IN (3,6,9,12) THEN 1
                                           ELSE 0 END CRF,* FROM [Presentation].[vDimCommissionData]) c
WHERE ( c.RevenueDate >= DATEADD( QUARTER, DATEDIFF( QUARTER, 0, GETDATE()), 0) AND CRF = 1)  
OR (c.RevenueDate >= DATEADD( QUARTER, DATEDIFF( QUARTER, 0, GETDATE()) - 1, 0) AND CRF = 0);
 --If revenue date is greater than or equal to the 9th day in the second month of the quarter,   We will display current quarters information and onwards else Previous quarter information and onwards 

END TRY

BEGIN CATCH
          SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

排查分析与解决建议 #

核心问题点 #

1. 事务逻辑拆分隐患：原代码将删除和插入拆分为两个独立事务，删除完成后直接提交，插入操作无事务保护。若Job执行时插入遇到隐性问题（如权限不足、视图无数据），会静默失败且无法回滚已提交的删除，导致表为空。
2. 账号权限差异：手动执行的账号与SQL Agent Job的服务账号权限不同。Job账号可能缺少[Presentation].[vDimCommissionData]的读取权限，或[Fact].[CommissionLiveData]的插入权限，导致插入无数据且不触发报错。
3. 日期逻辑执行上下文差异：GETDATE()在Job执行时取SQL Server服务器时间，手动执行时取客户端时间，若两者时间不一致，会导致CRF计算结果异常，WHERE条件过滤掉所有数据，最终插入0行。

解决步骤 #

• 确认事务逻辑有效性：更新后的代码已将删除和插入放在同一事务中，确保操作原子性，这是正确的优化方向。
• 检查Job账号权限：验证Job执行账号是否具备：
  • [Presentation].[vDimCommissionData]的读取权限
  • [Fact].[CommissionLiveData]的删除、插入权限
• 验证日期逻辑一致性：在SQL Server服务器上手动运行以下语句，确认CRF值和过滤结果：

  SELECT CASE WHEN MONTH(GETDATE()) IN (2,5,8,11) AND (DATEPART(DAY, GETDATE())) > 8 THEN 1 
              WHEN MONTH(GETDATE()) IN (3,6,9,12) THEN 1
              ELSE 0 END CRF;
  -- 查看视图数据是否符合过滤条件
  SELECT * FROM [Presentation].[vDimCommissionData] c
  WHERE ( c.RevenueDate >= DATEADD( QUARTER, DATEDIFF( QUARTER, 0, GETDATE()), 0) AND CRF = 1)  
  OR (c.RevenueDate >= DATEADD( QUARTER, DATEDIFF( QUARTER, 0, GETDATE()) - 1, 0) AND CRF = 0);
  

• 添加执行日志：在存储过程中加入日志记录，跟踪插入行数和执行状态：

  -- 插入操作后添加
  DECLARE @InsertCount INT = @@ROWCOUNT;
  INSERT INTO [dbo].[ProcExecutionLog] (ProcName, ExecutionTime, InsertedRows)
  VALUES ('Dim.Test', GETDATE(), @InsertCount);
  

• 查看Job执行日志细节：在SQL Server Agent的Job历史记录中，排查是否存在隐性错误提示，部分非致命错误不会标记Job失败，但会在日志中记录。

内容的提问来源于stack exchange，提问作者xorpower