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:

  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)'  .

No comments:

Post a Comment