Can multiple exec commands be run sequentially with SQL Server Agent

Transact-SQL
,
1

Hi

I've set up SQL Server Agent job to run about 20 execs statements in a row but it does't seem to run through.

E.g The job contains
exec sp_unfile_report 123
exec sp_unfile_report 456
exec sp_unfile_report 789
and repeats for 17 more times.

I set scheduled the SQL job last night which ran successfully with not error but I don't think is ran properly as the job only took 5 seconds when I would expect it to take at least 30 minutes to complete.

Many thanks if anyone can advise
Kind Regards
James

P.s Thanks to everyone for helping me in my past Topics that I've posted.

2

One way would be to wrap the executes in try/catch if sp_unfile_report returns error information

3

HI djj

Should this work, if I put inside the Step of the job on its own

`begin

begin try
	exec sp_unfile_report 123
	exec sp_unfile_report 456
	exec sp_unfile_report 789 
end try

begin catch
    
end catch

end`

Cheers
James

4

I would think this might work better:
begin try
exec sp_unfile_report 123
end try

begin catch
    ...    
end catch

begin try
	exec sp_unfile_report 456
end try

begin catch
    ...    
end catch

begin try
	exec sp_unfile_report 789 
end try

begin catch
    ...    
end catch
1 Like
5

Hi djj
I'll give it a try and add a post to this topic to let you know how it goes :smile:

Thanks
James

6

Hi djj

I've just tested by running two execs as you outlined and it ran though fine. Thanks very much.

Cheers
James

7

I'd make each one a separate step, and change the step conditions to "go to the next step" even on failure. That will also make it much easier to change the order in which procs run, should you ever need to.

1 Like