May 29, 2008 at 10:51 am
I'm setting up a Maintenance Plan to do a backup for me in the night of my SQL Databases but before the backup starts I have created a SQL Server Agent Job that runs a DOS batch (.bat) file using CmdExec. The problem is I need this process to finish processing before the rest of the tasks run in the Maintenance Plan. It seems like SQL Server Agent Job Task tells the batch file to start running (and it runs fine) but it just continues to the next task and does not wait for it to finish.
Is there any way I can configure this to wait before proceeding?
Thanks, Mark
May 30, 2008 at 12:55 pm
Either include the batch file process in the maintenace plan job as a first step or
check the file existance using xp_CMDSHELL OR some other extended proc and move to the next step...
MohammedU
Microsoft SQL Server MVP
May 30, 2008 at 2:58 pm
MohammedU (5/30/2008)
Either include the batch file process in the maintenace plan job as a first step orcheck the file existance using xp_CMDSHELL OR some other extended proc and move to the next step...
MohammedU,
None of those options will work because this is being called from a maintenance plan using the Execute SQL Server Agent Job Task. That task starts the agent job - and returns with the status of whether or not the agent job was started, and does not wait for the completion of that job.
I suppose you could use an Execute T-SQL Statement Task, but then you have to enable xp_cmdshell and I am not sure that is what is wanted here.
So, the only way to do this is (without enabling xp_cmdshell) is:
1) Do not schedule your maintenance plan
2) Open the Agent Job that executes the batch file
3) Add a new step following the step that executes the batch file
4) Change the type to SQL Server Integration Services
5) Select the server where the maintenance plan lives
6) Select the maintenance plan you want to run
So, we now have the SQL Agent job with two steps. The first step calls your batch file and the second step runs the maintenance plan. You will also have an agent job for the maintenance plan that is not scheduled - but, that should not be a problem.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2008 at 12:15 am
I havent executed batch files in SQL 2005 using the SQL server agent from the job scheduler yet, but surely it should wait for the completion of the step before continuing on the next step of the schedule? Thats the whole idea of having steps in the schedule, first do step 1, and upon completion, do step 2, etc.
Any specific reason for this behaviour?
June 3, 2008 at 1:59 pm
The Execute SQL Server Agent Job Task starts an agent job - but does not wait for that agent job to complete.
The is the same as executing the following:
Execute dbo.sp_start_job 'My Agent Job';
This procedure does not wait until the agent job has completed. It returns the status of whether or not it was able to start the job.
The Task does exactly the same thing and reports back whether or not it was able to start the job. If that job takes one minute, 30 minutes or several hours to process - the task (and procedure) will return almost immediately with a success or failure message based upon whether or not the agent job was started.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 3, 2008 at 2:34 pm
I may be misunderstanding something here, but SQL Agent runs steps sequentially. It finishes each one, tests for success or failure, then does whatever has been set as the "next thing to do", based on the success or failure.
I have many jobs that run this way. Finish step one, on success go to step two, on failure, send an alert; finish step two, on success go to step three, on failure, send an alert; and so on.
If step one of a job runs a batch file by using xp_cmdshell, it won't finish and report success/failure till xp_cmdshell reports completion. Books Online says, "xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed."
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 3, 2008 at 3:50 pm
GSquared (6/3/2008)
I may be misunderstanding something here, but SQL Agent runs steps sequentially. It finishes each one, tests for success or failure, then does whatever has been set as the "next thing to do", based on the success or failure.I have many jobs that run this way. Finish step one, on success go to step two, on failure, send an alert; finish step two, on success go to step three, on failure, send an alert; and so on.
If step one of a job runs a batch file by using xp_cmdshell, it won't finish and report success/failure till xp_cmdshell reports completion. Books Online says, "xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed."
Yes, that is all correct - and that is exactly the same thing that happens when you use the Execute SQL Server Agent Job Task. The problem is that all this task does is START an agent job. This task does not run the agent job, it does not process the steps in the agent job, it does nothing more than start the agent job. If the task was successful in starting the agent job - it reports back success. If the task was not successful in starting the agent job - it will report back failure.
If your process needs to wait for the agent job to complete successfully before continuing - then you cannot use this method to start that agent job - because your next step will start as soon as this task has started the agent job, not when that agent job has completed.
You are correct about running 'xp_cmdshell' - but, then you are probably using the Execute T-SQL Statement Task which executes the commands in that statement and does not return until the statements complete successfully.
If xp_cmdshell is not enabled - you cannot use the Execute T-SQL Statement Task to run it - and the only way I know of to run an OS command (e.g. batch file) is to create an agent job that does not use xp_cmdshell
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 4, 2008 at 12:27 pm
I just created a job in my ProofOfConcept database.
First step, run a T-SQL command to insert a start time into a table.
Second step, run a .BAT file that takes about 10 seconds to run, using the CmdExe job type (no use of xp_cmdshell).
Third step, run a T-SQL command that inserts end time into the same table as step 1.
Each step except the last one is set to exit and log failure for the job if the step fails, and to go on to the next step if it succeeds. Last step exits and logs success.
The job ran sequentially. It waited for the batch file to finish running before it started the next step.
I'm not sure what you have set up differently, but it definitely was sequential, not parallel, on my machine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 4, 2008 at 1:52 pm
Again, yes - that is correct when you setup everything in a SQL Agent Job. Now, try setting that up to run as a maintenance plan and use the Execute SQL Agent Job Task.
First task in the maintenance plan - Execute SQL Agent Job Task that runs a batch job.
Second task - set to start on success of the first step, any task you want.
The second task will start before the agent job that was started from the first task completes.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 5, 2008 at 1:19 pm
I think you're misunderstanding me. I'm saying DON'T set it up the way you're setting it up. INSTEAD, set it up as a job, with the BAT file as the first step, and the maintenance plan as the second step.
Yes, you are right about it working the way it's working for you in the other set up. I'm suggesting setting it up a slightly different way, so that it will work as desired.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 5, 2008 at 2:47 pm
I agree - and that was my recommendation to the OP also. So, basically - we were saying the same thing...:)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 5, 2008 at 3:11 pm
Jeffrey Williams (6/5/2008)
I agree - and that was my recommendation to the OP also. So, basically - we were saying the same thing...:)
Pretty much!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply