July 11, 2011 at 10:36 am
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.
Thanks lot in advance
July 11, 2011 at 10:43 am
That's the only alternative I could get to work..
EXEC [linked Servername].msdb.dbo.sp_start_job N'JobName'
July 11, 2011 at 10:47 am
Hi
This doesnt work 🙁
It says Server '1.1.1.1' is not configured for RPC.
July 11, 2011 at 10:50 am
USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'LOCALSERVER', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LOCALSERVER', @optname=N'rpc out', @optvalue=N'true'
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply