CODEWITHSUNDEEP
sql-serverstored-proceduressql-job
xorpower
xorpower

Reputation: 19003

SQL Agent Job executes delete statement instead of Delete-Insert

I have written a stored procedure, code shown below. I have scheduled a job to execute this stored procedure. The job executes the procedure in 10 seconds and deletes the table but DOESN'T insert any rows. The stored procedure, when executed manually, deletes and inserts 2800 rows. The stored procedure syntax is correct. It looks like the job only deletes the table and stops executing thereafter. The job doesn't reports any errors.

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;

Updated code:

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;

Can you please help me with what exactly I am doing wrong?

Upvotes: 1

Views: 69

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76943

You missed committing the transaction if the insert-select is successful.

Also, you will need to test doing it step-by-step and see whether the select used in the insert-select is not empty.

But the solution seems to be to just commit the transaction if there was no error.

Upvotes: 1

Related Questions