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,552 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,126 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 40,741 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,891 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.