asked
T-SQL Linked Server Query to Stop a JOB and Start it again
Hi I need a Tsql Query to stop a job - referesh it and start it again. I have a job which runs for long hours and fails. So we manually stop it and start it again everyday. I am planning to create another job running periodically - which stop the this job and start it again. Can you please advise me is my below approach is correct ? Using linked server OPEN QUERY SELECT * FROM OPENQUERY([1.1.1.1], ' EXEC msdb.dbo.SP_START_JOB N''JobName''; ') I get this below error : Cannot process the object " EXEC msdb.dbo.SP_START_JOB N'Jobname'; ". The OLE DB provider "SQLNCLI10" for linked server "1.1.1.1" indicates that either the object has no columns or the current user does not have permissions on that object.
I think your issue lies in the fact that OPENQUERY expects a rowset by return - and the SP doesn't give you one (just a message). Have you tried `EXEC [1.1.1.1].msdb.dbo.sp_start_job N'JobName'` This should run the start procedure on the linked server (but you may need RPC to be turned on on the linked server definition). I'd also ask why you have a job that runs for long hours and then fails, as this doesn't seem ideal.
