Cannot add SQL Agent Job step with server AlwaysOn Availability Group Listener

  • Yagee Dreys

    SSC Veteran

    Points: 208

    Hi,  we have setup our High availability environment although we know existing SQL Agent Jobs will not be replicated to the secondary node. We use a tool that will migrate these SQL agent jobs to the secondary however we ran into a problem wherein the server name is fixed to the primary or the current local server (@server = @@Servername) , so when it failover and we attempt to migrate it, it carries the primary sever name which is incorrect.


    exec msdb..sp_add_jobstep @job_id = '{XXX}'
    , @step_name = 'FirstStep'
    , @step_id = '1'
    , @subsystem = 'TSQL'
    , @command = ''
    , @cmdexec_success_code = 0
    , @server = @@Servername
    , @database_name = 'MyDB'
    , @database_user_name = ''
    , @retry_attempts = 0
    , @retry_interval = 0
    , @output_file_name = 'C:\Test.err'
    , @flags = 4

    @@Servername returns the local servername upon creation

    We tried pointing it to the Listenername so it will always point to the primary but it throws this error "The specified '@server' is invalid (valid values are returned by sp_helpserver)."

    , @server = 'Listenername'

    I guess the listenername is not being recognized at this point.

    Is there another option wherein we can create this job with a server variable  that always run on local

  • Yagee Dreys

    SSC Veteran

    Points: 208

    We already found the fix for this, we just leave the server to blank 


    @server = ""

    and the job will always run on the local server

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply