SQL Server Agent job runs but does not perform SQL in a particular job step.

Question

SQL Server Agent job runs but does not perform SQL in a particular job step.

forco02

Old Hand

Points: 358
More actions
October 5, 2010 at 5:01 am

#224715

I have a 4 step job defined in SQL Server Agent, all steps are set to "Quit the job reporting failure" and on success "go to next step" each step runs Transact-SQL but my step 3 never alters the data in the table it runs against and never reports a failure. The Parse button says the SQL is correct, and if I run the SQL on the table through SSMS it changes the table as expected???
The simple query is as follws
BEGIN TRANSACTION SageDates
GO
USE LIVE;
GO
/* alter the sage Claim_To date */
UPDATE [Supporting].[Sage_Contract_Numbers]
SET Sage_Claim_To = '11/08/2009'
WHERE Sage_Expense_Budget = 0
AND Sage_Claim_From < '11/08/2009'
GO
UPDATE [Supporting].[Sage_Contract_Numbers]
SET Sage_Claim_To = Sage_Claim_From
WHERE Sage_Expense_Budget = 0
AND Sage_Claim_From >= '11/08/2009'
GO
COMMIT TRANSACTION SageDates
GO
Job log stated:
Job 'Populate Sage tables' : Step 3, 'Alter Claim_To Date' : Began Executing 2010-10-05 07:59:59
Job 'Populate Sage tables' : Step 4, 'Mail Changes' : Began Executing 2010-10-05 07:59:59
Mail queued. [SQLSTATE 01000]
Mail queued. [SQLSTATE 01000]
Step 4 is then executed so the job did not fail?
As stated above I then run the query in SSMS and it worked ok changing 180, and 370 rows respectively.
Can anyone help please?
Thanks,
Colin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 1

forco02 (10/5/2010)
I have a 4 step job defined in SQL Server Agent, all steps are set to "Quit the job reporting failure" and on success "go to next step" each step runs Transact-SQL but my step 3 never alters the data in the table it runs against and never reports a failure. The Parse button says the SQL is correct, and if I run the SQL on the table through SSMS it changes the table as expected???
The simple query is as follws
BEGIN TRANSACTION SageDates
GO
USE LIVE;
GO
/* alter the sage Claim_To date */
UPDATE [Supporting].[Sage_Contract_Numbers]
SET Sage_Claim_To = '11/08/2009'
WHERE Sage_Expense_Budget = 0
AND Sage_Claim_From < '11/08/2009'
GO
UPDATE [Supporting].[Sage_Contract_Numbers]
SET Sage_Claim_To = Sage_Claim_From
WHERE Sage_Expense_Budget = 0
AND Sage_Claim_From >= '11/08/2009'
GO
COMMIT TRANSACTION SageDates
GO
Job log stated:
Job 'Populate Sage tables' : Step 3, 'Alter Claim_To Date' : Began Executing 2010-10-05 07:59:59
Job 'Populate Sage tables' : Step 4, 'Mail Changes' : Began Executing 2010-10-05 07:59:59
Mail queued. [SQLSTATE 01000]
Mail queued. [SQLSTATE 01000]
Step 4 is then executed so the job did not fail?
As stated above I then run the query in SSMS and it worked ok changing 180, and 370 rows respectively.
Can anyone help please?
Thanks,
Colin

Hi

Colin,

There is all is well, Just Run the step 3 and there after step 4, through SSMS, your step 3 is altering proper what it is write to do? its doing. but the 4th step is canceling the step 3. Its come as previous, the is If all above code are step 1,2,3,4. in my opinion only.

Ali
MCTS SQL Server2k8

forco02 Old Hand Points: 358 More actions · Answer 2

Hi ali,

If I start the job at step 3 it still does not perform the changes on the Data, but step 4 completes successfully too.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Colin

Could your login and the SQL Agent login be interpreting the data 08/11/2009 differently? If you express it in the form 20091108 then there will be no ambiguity.

John

forco02 Old Hand Points: 358 More actions · Answer 4

Hi John,

Just tested your theory, and it looks good! Thanks very much you have save much hair pulling 🙂

Colin.