I gang

SQL Job dependency

Hi,

I wanted to create a process to control SQL Server Jobs that depend on each other.

Job Control Process

The process controls SQL Server Jobs that depend on each other ie. if Job1 depends on Job 2 ie. Job2 has to complete, before Job1 can start, then the process will control the job starting.

At the moment the jobs are scheduled to run at specific times eg. Job2 is scheduled to start at 1pm and Job1 at 2pm. The issue is that if Job2 runs for over an hour, the next job (Job1) would fail. The process controls job start times based on Job levels (not Job steps).

The following tables in the Operations database manage the control process.

• AS_JobSchedules - A table that contains the jobs

• AS_Dependencies - A table that contains a list of Jobs and their dependencies.

? JobID FK to JSID in JobSchedules,

? JobDependencyID FK to JSID in JobSchedules

? Priority specifies priority of dependency in case 1 job depends on more than one job.

• AS_JobRunLog - A table that logs RunStartdate, RunCompletedDate or RunFailedDate of every job that runs.

• AS_JobRunWaitLog - A table that logs the job that’s waiting, the job that’s causing the wait and duration of wait.

Every Job will have 2 extra steps around the normal job steps of the job.

1. Begin Check Job dependency

2. Whatever the job steps are...

3. Log Job dependency

The broad process might be:

1. Job1 begins – Step1 (Begin Check Job dependency stored Procedure)

a. INSERTs a record in the AS_JobRunLog table with RunStartDate (leaving RunCompletedDate and RunFailedDate as NULL)

b. Checks AS_Dependencies table for its dependency job/s. It then checks to see if that job's entry in the AS_JobRunLog table has a RunCompleted that is not null and within 24 hours of the RunStartDate of this job (ie the job that ran last actually completed).

c. If it’s completed, the Steps 1 completes successfully and the next step can begin.

d. If not; ie the job is still running, it INSERTs a record in the AS_JobRunWaitLog.

e. It uses a WAITFOR and hangs back for 10 minutes and tries again until it finds the dependency job has completed. The job step then completes successfully and the next step can begin.

2. Step2 – The actual step of that job.

3. Step 3 - (Log Job dependency stored Procedure)

a. If the previous job step succeeds, Step 3 stored procedure UPDATEs the record in the AS_JobRunLog table with RunCompletedDate (leaving RunFailedDate as NULL)

b. If the previous job step fails, Step 3 stored procedure UPDATEs the record in the AS_JobRunLog table with RunFailedDate (leaving RunCompletedDate as NULL)

So in summary, I need someone to write 2 stored procedures that achieve the above process using the tables listed.

1. usp_BeginCheckJobDependency stored Procedure

2. usp_LogJobDependency stored Procedure

Please quote for this.

Thank you.

Færdigheder: Software Arkitektur, SQL

Se mere: software architecture job, quote architecture, leaving job, job summary, job jobs, every job, architecture quote, sql server job, process server jobs, jobs operations, sql software, stored procedure list, create database stored procedures, sql server stored procedures, record checks, stored procedure create sql, control process, sql entry, null software, jobid, database job quote, check sql, create table sql using, list stored procedures, operations log

Om arbejdsgiveren:
( 28 bedømmelser ) London, United Kingdom

Projekt-ID: #4118658

Tildelt til:

weonlydo

Hi, I would be pleased to write your two stored procs for you. Your procs will be delivered on a silver platter and fully tested. Please refer to PM for references.

$160 USD in 2 dage
(17 bedømmelser)
4.8

13 freelancere byder i gennemsnit $273 for dette job

siberiangirl

Please, check PM.

$250 USD in 5 dage
(131 bedømmelser)
5.9
osinee

Hi I'm interested that project - please check PM for more details.

$250 USD in 3 dage
(5 bedømmelser)
3.5
luckyfred

functional needs, I'd like to do that, would you mind increase your budget?

$1300 USD in 3 dage
(1 bedømmelse)
2.1
AliSafder

Hi, Please consider my proposal for the task. Regards, Muhammad Ali Safder

$150 USD in 5 dage
(2 bedømmelser)
1.2
Prateek3288

Hi Asim, Please check the PM for details. Regards, Prteek3288

$250 USD in 4 dage
(0 bedømmelser)
0.0
DrFreelancer2012

more details

$1500 USD in 30 dage
(0 bedømmelser)
0.0
maxatcn

I have read your idea, and I am curious the solution you are talking, let's discuss more details.

$200 USD in 5 dage
(0 bedømmelser)
0.0
kushagra2011

I am SQL Architech having 10+ working experience and I have already design such job functionality. SO I can ensure you 100% satisfation.

$100 USD in 4 dage
(0 bedømmelser)
0.0
deepakgiria

Hi, I have experience in SQL and reporting using various tool and can be potential asset for your project. Please check your message box for more details.

$150 USD in 2 dage
(0 bedømmelser)
0.0
raviteja2020

I have excellent experience in PLSQL and SQL. I am ready to start.

$100 USD in 5 dage
(0 bedømmelser)
0.0
nojnish

please refer PMB

$175 USD in 5 dage
(0 bedømmelser)
0.0
freedevs

Hey There! I am interested in doing this project for you.

$210 USD in 5 dage
(0 bedømmelser)
0.0
synergicinfotech

Hi, we have team working on web applications, SQL, PLSQL and database technologies. We assure the quality of work and delivery. We have successfully completed many projects in past and it is very easy project. Th Mere

$150 USD in 5 dage
(0 bedømmelser)
0.0
outsource9999

understand requirements; can do it in 24h

$350 USD på 1 dag
(0 bedømmelser)
0.0