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.