Multiple Concurrent Instances of the same Server Job/Job Seq

[palmeal]

The scenario that I have is as follows:

I have a table on a MSSQL Server that contains the following information:

ServerName Job Name Processing
BKP_ANY2 Ptfo_Extract N
ST_PPSA Ptfo_Extract N
BKP_ANY4 Ptfo_Extract N
..
..
..
Up to 10 servers

The ServerName column is the name of a Sybase server.


I have a controlling Server Job that contains only Basic Code. The first thing that the Server Job does is to call another Server job that connects to the above MSSQL Server and accesses the table above for the first Sybase Server name where the Processing column is set to 'N'.
This value is then updated to 'Y' and the Sybase ServerName is passed to the underlying Server Job as a parameter. A data extraction process then starts running against this server.

What I want to be able to do is run this process up to 10 times concurrently but with only one instance on each Sybase server. I can't see any way to do this and don't want to duplicate the controlling Server Job 10 times but feel that I may have to.
There shouldn't be any conflict between these 10 instances in term of file names as any that I create in the Server Jobs will have unique names as they are pre-fixed by #SERVERNAME#. I guess the Director process would have problems differentiating between "identical" jobs running.


Does anyone have any ideas on how I can do this ?



For the example above:

Instance 1 of the Server Job would connect to the MSSQL server and look for a Sybase ServerName where Processing = 'N'. The Server Job would update this flag to 'Y' and then start running an extraction process against the Sybase Server - in the first case BKP_ANY2.

Instance 2 of the Server Job would connect to the MSSQL server and look for a Sybase ServerName where Processing = 'N'. The Server Job would update this flag to 'Y' and then start running an extraction process against the Sybase Server - in the first case ST_PPSA.

Instance 3 of the Server Job would connect to the MSSQL server and look for a Sybase ServerName where Processing = 'N'. The Server Job would update this flag to 'Y' and then start running an extraction process against the Sybase Server - in the first case BKP_ANY4.

and so on until all required instances are running.

[vmcburney]

You're in luck, this is straight forward mult instance functionality. If you look at the job properties of your server job you will see a check box for "allow multiple instances" which lets you run multiple copies of a job by adding a suffix to the job name:
jobname.instancename

Eg. JobProcessServer.BKP_ANY2, JobProcessServer.ST_PPSA etc. If you already have the code that attaches and runs the job it is an easy step to turn it into a loop and turn the server name into the instance name.

Have a look in the server job developers guide for details on multiple instance jobs.