Solved

Commiting each T-SQL steps in a SQL Server Agent Job

Posted on 2004-09-25
1
1,263 Views
Last Modified: 2012-06-27
I have a SQL Server Agent job that has several T-SQL steps that use sp_start_job to start individual jobs one after another. But the problem is that some of the called jobs (which are comprised of T-SQL xp_cmdshell steps) does not get committed until the end of the of the job. This how the process flow of the job is:

Main Job:
-----------
Step one: T-SQL => Exec sp_start_job 'Job 1'   On Success: Goto Next Step     On Failure: Goto Step three.
Step two: T-SQL => Exec sp_start_job 'Job 2'   On Success: Goto Next Step     On Failure: Goto Next Step.
Step three: T-SQL => Exec sp_start_job 'Job 3' On Success: Quit with Success On Failure: Quit with Failure.


Job 1:
-------
Step One: T-SQL => Exec xp_cmdshell => 'Delete *.* C:\Run Stats\'  On Success: Quit with Success On Failure: Quit with Failure.

Job 2:
-------
Step One: CmdExec => 'Executes COBOL Program One that updates the DB and saves process run stats in C:\Run Stats' On Success: Goto Next Step On Failure: Goto Next Step
Step two: CmdExec => 'Executes COBOL Program two that updates the DB and saves process run stats in C:\Run Stats' On Success: Goto Next Step On Failure: Goto Next Step
Step Three: CmdExec => 'Executes COBOL Program Three that updates the DB and saves process run stats in C:\Run Stats' On Success: Goto Next Step On Failure: Goto Next Step
Step Four: CmdExec => 'Executes COBOL Program Four that updates the DB and saves process run stats in C:\Run Stats' On Success: Quit with Success On Failure: Quit with Failure.

Job 3:
-------
Step One: T-SQL => Exec xp_cmdshell => 'XCOPY C:\Run Stats\  /S/Y C:\YYYY-MM-DD Run\Run Stats'  On Success: Quit with Success On Failure: Quit with Failure (where YYYY-MM-DD the todays run date).

As you noticed above that Main Job is the Overall Batch Cycle scheduled to run every evening during off hours. Job 1 is to initialize C:\Run Stats\ by deleting previous day run stats files. Job 2 is basically calls to COBOL executables, which results in updating database and creating a program run stats file in the C:\Run Stats\ folder. Finally, Job 3 is used to archive the current run stats into a new folder named as YYYY-MM-DD Run.

The problem is that when the Main Job (cycle) is run the C:\YYYY-MM-DD Run\Run Stats\ shows the files from previous day Run Stats (which run stats from YYYY-MM-DD - 1). Eventhough the current C:\Run Stats\ shows the today's Run Stats. But, when I scheduled the Job 1,2,3 individually one after another with 10 minutes gap between jobs. Everything works fine, which is the C:\YYYY-MM-DD Run\Run Stats\ shows the current run stats. Somehow, SQL Server Agent does not commit each steps on completion but at the end of the job.

Does any know how to make the SQL Server Agent Job commits each steps on completion of the step not the Job? P.S. I have put Begin Transaction and Commit Transaction in each step of the Main Job but no luck. Still getting previous day run stats in the C:\YYYY-MM-DD Run\Run Stats\ folder.

Thanks in advance,
Baluch.
0
Comment
Question by:baluch
1 Comment
 
LVL 7

Accepted Solution

by:
FDzjuba earned 100 total points
ID: 12154266
Well from what i know, for example if you run job from SP then SP will not wait untill job is finished and will continue processing without waiting for job to finish. That is exactly what happens in your main job.

What you can do is, use WAITFOR command. In yout main procedure create a loop that delays execution untill sees all files from Run Stat deleted/ created.
Or another thing is to pass parameters (flags) by use of ##temptable from job Step 1,2,3 to main job. In your main job just loop untill these params are passed.

Hopefully you get the point
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
SQL Replication question 9 43
SQL Improvement  ( Speed) 14 28
Query to capture 5 and 9 digit zip code? 4 21
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question