Post reply

T-SQL Linked Server Query to Stop a JOB and Start it again

  • July 11, 2011 at 10:36 am

    #241330

    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

    #1351204

    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

    #1351207

    Hi

    This doesnt work 🙁

    It says Server '1.1.1.1' is not configured for RPC.

  • July 11, 2011 at 10:50 am

    #1351209

    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