Unable to correctly pass parameter to command parameter in Azure Elastic job stored procedure [jobs].[sp_add_jobstep]

mo boy 396 Reputation points
2020-09-24T12:11:44.9+00:00

Dear Experts,

I have this on premise job which purges data weekly on my database. I have replicated similar job using Elastic job agents for Azure SQL DB. The syntax for the job script is below. I see that the job is not running on Elastic job agents when I pass the parameters marked in bold. It doesn't seem to accept parameters within the stored procedure. Is there any way by which I can get this to work. Could you please advise?

EXEC jobs.sp_add_jobstep @Job _name='WeeklyPurge',
@commandcommand ='
EXEC [dbo].[Weekly_Data_Purge]
@dbName = TestDB
,@schemaName = dbo
,@tableName = SampleTables
,@dateFieldName = SystemDate
',
@credential_name='JobExec',
@target _group_name='AzureDBServers'

Azure SQL Database
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,654 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,206 Reputation points
    2020-09-25T17:43:59.107+00:00 
    EXEC jobs.sp_add_jobstep 
@job_name = 'WeeklyPurge',
@command = N'EXEC [dbo].[Weekly_Data_Purge]
 @dbName = ''TestDB'',
 @schemaName = ''dbo'',
 @tableName = ''SampleTables'',
 @dateFieldName = ''SystemDate''',
@credential_name = 'JobExec',
@target_group_name = 'AzureDBServers';
    2 people found this answer helpful.
    0 comments
  2. Olaf Helper 45,096 Reputation points
    2020-09-24T12:59:02.527+00:00

    The parameter names are mostly wrong, see sp_add_jobstep (Transact-SQL) for correct parameter names.

  3. Monalv-MSFT 5,901 Reputation points
    2020-09-25T07:04:51.56+00:00

    Hi @mo boy ,

    Please refer to the following example and link:

    Add a job step named process step. This step runs the stored procedure:
    DECLARE @SSIScommand as NVARCHAR(max)
    SET @SSIScommand = '/ISSERVER "\"'+@package+'\"" /SERVER "\"'+@servername+'\"" '+@params+' /CALLERINFO SQLAGENT /REPORTING E'
    EXEC msdb.dbo.sp_add_jobstep
    @Job _name = @Job ,
    @STEP SRL _name = N'process step',
    @subsystem = N'Dts',
    @commandcommand = @SSIScommand

    Parameterize sp_add_jobstep for SSIS

    Best Regards,
    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.