Solved

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

Posted on 2004-09-25
1
1,264 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

789 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