Can we create elastic jobs from data base other than the host DB?

Question

I have an elastic job agent setup on database X. I want to create jobs but from a different database. Right now I am getting

SQL Error [2812] [S0062]: Could not find stored procedure 'jobs.sp_add_target_group'.

Error position:

Can someone help/ advice here.

Answer 1

In Azure SQL Database, when working with Elastic Jobs, the job agent is set up on a specific database (host database). However, the stored procedures and necessary objects for managing Elastic Jobs must be present in that host database. It seems like you are trying to execute a job creation procedure from a database other than the host database, which is causing the error.

Make sure you are connected to the database where the Elastic Job agent is set up. Use the host database to create and manage Elastic Jobs. Here’s an example of how to create a job:

USE [X];    -- Create a target group

EXEC jobs.sp_add_target_group @target_group_name = 'MyTargetGroup';

-- Add databases to the target group

EXEC jobs.sp_add_target_group_member

 @target_group_name = 'MyTargetGroup',

 @database_name = 'TargetDatabaseName',

 @server_name = 'TargetServerName';

-- Create a new job

EXEC jobs.sp_add_job @job_name = 'MyElasticJob';

-- Add a job step

EXEC jobs.sp_add_jobstep 

 @job_name = 'MyElasticJob',

 @command = 'SELECT * FROM my_table;', 

 @credential_name = 'MyCredential',

 @target_group_name = 'MyTargetGroup';

Make sure all job-related commands are executed in the context of the host database. For example:

USE [X]; t database

-- Start the job

EXEC jobs.sp_start_job @job_name = 'MyElasticJob';

Finally verify that the user account you are using has the necessary permissions to manage Elastic Jobs in the host database. Typically, you need to have the jobs_admin role.