Link to home
Start Free TrialLog in
Avatar of baluch
baluch

asked on

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

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.
ASKER CERTIFIED SOLUTION
Avatar of FDzjuba
FDzjuba

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial