Solved

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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now