I have a SQL Server Agent job that runs a series of stored procedures. It is a single step job and in the command window of the package it says the following:
exec fb_GetDateSource '6A0DA1EB-D349-4D97-AAFC-822343D8EB49'
exec fb_DeliverFile '2344EC77-0D34-44A9-8A10-E5FEA1F6F218'
exec fb_DeliverFile '0BF456B8-24E5-4B0E-8FDA-F31C2D397B3A'
exec fb_DeliverFile 'C42EA4AA-3179-4344-BE24-5CEBA435F4FD'
So the first line collects a bunch of data into a temporary table and then the next 3 will use that data to deliver data files to our customers. We are having some trouble with old data getting into the files.
When I run a command like that are the stored procedures all called sequentially (what I assumed) or could they be running in parallel?
I'm moving the first call out into a separate step. So it will run it in the first step and then deliver in the second step. Ultimately testing will show me if it works but I'm curious if these stored procedures could be running in parallel with each other? That would explain our issue if that is the case.
In your code:
exec fb_GetDateSource '6A0DA1EB-D349-4D97-AAFC-822343D8EB49';
exec fb_DeliverFile '2344EC77-0D34-44A9-8A10-E5FEA1F6F218';
exec fb_DeliverFile '0BF456B8-24E5-4B0E-8FDA-F31C2D397B3A';
exec fb_DeliverFile 'C42EA4AA-3179-4344-BE24-5CEBA435F4FD';
Stored procedures are executed serially (one after another). If you want parallelism you could achieve it by creating multiple jobs and launch them from main job.
Main job: sp_start_job will start job and return control to main program.
EXEC msdb.dbo.sp_start_job @job_name=@MyJobName; -- job name 1
EXEC msdb.dbo.sp_start_job @job_name=@MyJobName; -- job name 2
EXEC msdb.dbo.sp_start_job @job_name=@MyJobName; -- job name 3
EXEC msdb.dbo.sp_start_job @job_name=@MyJobName; -- job name 4
Alternatively, you could create SSIS package with Parallel Task and run this package by SQL Server Agent.