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

• Yagee Dreys

  Old Hand

  Points: 308

  More actions

  March 8, 2017 at 1:29 am

  #341465

  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

  Old Hand

  Points: 308

  More actions

  March 9, 2017 at 12:33 am

  #1932457

  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