(Advanced knowledge of SQL programming stored procedures, setup of SQL jobs is required)
Example we have two SQL jobs.
- The first SQL Job Intraday Transactions Refresh (SJITR) is an intraday transaction tables, that refresh data to a local database from a linked server cloud SQL database. It starts at 12AM. This job pulls data from previous day and updates the local transaction tables.
- The second SQL Job Report (SJR) generates a report based off on the data from previous day and sends an email. It starts at 8AM.
So every day the 2 jobs run back to back. The SJITR runs and it finishes around 3AM. The SJR runs at 8AM. This gives the SJITR leeway to finish before 8AM. One day the SJITR did not complete around 3AM. It was still continuing pass 8AM. At this moment we don’t know it was a problem until the SJR runs and the report didn’t show previous day’s data on the report. We look at the SQL job history of both jobs. We see that the SJITR didn’t finish until 8:30AM and that the SJR started at 8AM. We then look at the audit logs that we created for each of the stored procedures in the SJITR job to see what happened. We found that in one of the steps of was still processing the detail data which failed in the try catch logic 2 times and was completed on the third try catch logic. Then SJITR completed by 8:30AM and the SJR runs at 8AM was incomplete report.
So to resolve this issue, we create a table process_status. This table can be used for other things in the database not just for SQL job status.
CREATE TABLE [dbo].[process_status](
[taskname] [varchar](500) NOT NULL,
[processtype] [varchar](50) NOT NULL,
[comment] [varchar](500) NOT NULL,
— separated for illustration of data types for each field to be updated. There are 5 sets here.
[recordtype] [varchar](25) NOT NULL,
[subtype] [varchar](25) NOT NULL,
[varcharvalue] [varchar](256) NOT NULL,
[numericvalue] [numeric](19, 4) NOT NULL,
[intvalue] [bigint] NOT NULL,
[datevalue] [datetime] NULL,
[recordtype2] [varchar](25) NOT NULL,
[subtype2] [varchar](25) NOT NULL,
[varcharvalue2] [varchar](256) NOT NULL,
[numericvalue2] [numeric](19, 4) NOT NULL,
[intvalue2] [bigint] NOT NULL,
[datevalue2] [datetime] NULL,
[recordtype3] [varchar](25) NOT NULL,
[subtype3] [varchar](25) NOT NULL,
[varcharvalue3] [varchar](256) NOT NULL,
[numericvalue3] [numeric](19, 4) NOT NULL,
[intvalue3] [bigint] NOT NULL,
[datevalue3] [datetime] NULL,
[recordtype4] [varchar](25) NOT NULL,
[subtype4] [varchar](25) NOT NULL,
[varcharvalue4] [varchar](256) NOT NULL,
[numericvalue4] [numeric](19, 4) NOT NULL,
[intvalue4] [bigint] NOT NULL,
[datevalue4] [datetime] NULL,
[recordtype5] [varchar](25) NOT NULL,
[subtype5] [varchar](25) NOT NULL,
[varcharvalue5] [varchar](256) NOT NULL,
[numericvalue5] [numeric](19, 4) NOT NULL,
[intvalue5] [bigint] NOT NULL,
[datevalue5] [datetime] NULL,
[seq] [bigint] IDENTITY(1,1) NOT NULL
)
So, for the SJITR, we inserted a row to the process_status table name ‘SJITR’ and the rest of the fields blanks, zeros and NULL to date fields. Then we modified four stored procedures in SJITR to update this table by setting 1 to each of the intvalue/2/3/4 fields for taskname = ‘SJITR’. If each stored procedure successfully pulled data from the linked server. The last stored procedure in SJITR after it processes its task. It will reset the values for the intvalue/2/3/4 to zeros. Now on SJR, we modify the stored procedure here to include the following code at or near the beginning of the code.
declare @cnt1 int, @delaystart datetime, @startnow datetime, @subject varchar(500)
select @cnt1 = 1, @delaystart = getdate()
set @startnow = @delaystart
while (select intvalue+intvalue2+intvalue3+intvalue4 from process_status where taskname = ‘SJITR’) = 4
begin
waitfor delay ’00:30:00′ — Here we delay the process for every 30 mins until the process_status is all zero
if @cnt1 = 5
begin
— Send mail to users after 5 tries the SJR fails the process of SQL job. They will have to manually run the SQL job again.
— RAISERROR(‘Stop Execution ‘,16,1)
— return 0 to exit the code and Step 1 of the SQL job.
break
end
select @cnt1 = @cnt1 + 1
end
select @startnow = getdate()
if @cnt1 = 1
begin
set @subject = ‘*** Report generated for today.’– Set whatever message you want the users to know in subject heading.
end
else
begin
set @subject = ‘*** Report generated for today after delay of ‘ + convert(varchar(5),datediff(minute,@delaystart,@startnow)) + ‘ minutes of SJITR SQL job.’
end
— send email to let users know that it’s processing the report.
— continue to process code for generating report for SJR
This will delay the processing of SQL Job Reporting for whatever time delay you set it for. Remember you can change the counter and time delay to whatever you need it to be.
