March 8, 2017 at 1:29 am
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
March 9, 2017 at 12:33 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply