Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-09-25
1
Medium Priority
?
1,290 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 300 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

885 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