Sunday, February 6, 2011

Start SQL Server Agent job remotely

Sometimes it is necessary to perform administrative tasks on other SQL Servers, remotely.  As your inventory grows, using Remote Desktop to connect to every one of them can become a little inconvenient.  I administer my instances remotely, as needed.  For example, the statement below can be used to start a SQL Server Agent job remotely, on another SQL Server:

  DECLARE @sql VARCHAR(MAX)
  SET @sql  = ''


  DECLARE @SystemName VARCHAR(50)
  DECLARE @JobName     VARCHAR(50)
  DECLARE @StepName    VARCHAR(50)


  SET @SystemName = 'Other Server Name'
  SET @JobName = 'Agent Job Name'


  SET @sql = '' + @SystemName + '.msdb..sp_start_job @job_name = 
  ''' + @JobName + ''''


  --PRINT 'EXEC '+ @sql
  EXEC (@sql)

Why don't you comment out the EXEC, and un-comment the PRINT before running, just to see how things look.


Please don't forget, you can also use SQL Server Configuration Manager to connect to other servers at a higher level, and administer the services remotely.  See this topic in BOL:  'How to: Connect to Another Computer (SQL Server Configuration Manager)'  .

3 comments:

  1. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also 여성유흥알바

    ReplyDelete
  2. It is truly a well-researched content and excellent wording. I got so engaged in this material that I couldn’t wait reading. I am impressed with your work and skill. Thanks. Viec lam them 365

    ReplyDelete
  3. Waow this is quite pleasant article, my sister love to read such type of post, I am going to tell her and bookmarking this webpage. Thanks social work jobs London

    ReplyDelete