I have an issue where a stored procedure I built to process some data doesn't seem to work correctly when being run by the SQL Agent. If I manually execute the job everything works fine. I also get no errors.
The basic process is I have three source tables (A,B,C), each are for a different type of service. They are used to calculate a value, and since the calculation process is different each year, there are multiple results, so ID B43 will have result15, and result16, corresponding to the 2014/15 and 2015/16 financial years. The final result is stored in a master table that contains the ID, Type, and both result values for each record. To make this work, I have six stored procedures that run sequentially, first for the 14/15 calculation then for the 15/16 calculation.
The general structure of each stored procedure is to take the data, manipulate it based on some mapping tables and create a CalculationInput table, which is simply there to review the input into the calculation for audit checks. Once this is done, the actual calculation occurs with the result is stored in a temp table #Results. After the temp table is built, I create some indexes, and then call another stored procedure, passing in the financial year as a parameter, which takes the temp table and does a insert update on the master table. After that, the stored procedure ends, and the next one is called.
StoredProcA15 -> InsUpdProc StoredProcB15 -> InsUpdProc StoredProcC15 -> InsUpdProc StoredProcA16 -> InsUpdProc StoredProcB16 -> InsUpdProc StoredProcC16 -> InsUpdProc
As I said before, this works perfectly fine if I right click on the job and execute it. However if I then wait a week and look at the master table, new records won't be there, despite them being in the source table, as well as the CalculationInput table. If I then run the section of code that builds the temp table, the result appears there.
So my best guess at the source of the failure is the call to the insert/update procedure. Without knowing more about SQL concurrency, I am wondering if parent stored procedure isn't waiting for the insert/update procedure to finish before it ends and moves onto the next stored procedure.
Is this a possibility? and if so what is the best way to fix it.