How to execute sql agent job using wildcard?

• dan_27

  SSC Veteran

  Points: 259

  More actions

  July 27, 2018 at 7:14 am

  #355043

  Hi experts,
  How to execute an sql angent job using wildcard?
  From this:  EXEC MSDB.dbo.sp_start_job @Job_Name = 'JobName'
  To This:     EXEC MSDB.dbo.sp_start_job @Job_Name like 'MyJobEvery%'
  Is there another way to call the job WITHOUT exact name but using '%%'
  Thanks

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  July 27, 2018 at 7:44 am

  #1999239

  In this case, the equal sign is being used as an assignment operator, not a comparison operator.  In other words, you're assigning the value to the parameter, not checking to see whether the parameter equals the value.  LIKE cannot be used as an assignment operator.

  Also, I don't think you've thought through the ramifications of doing so.  It's entirely too easy for someone to set up a malicious job that you would blithely execute, because it just happens to meet the pattern you've set up  Sounds like a disaster waiting to happen.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• dan_27

  SSC Veteran

  Points: 259

  More actions

  July 27, 2018 at 8:24 am

  #1999247

  Thank for your reply. I got it "LIKE cannot be used as an assignment operator". Main point of my question is, is there a WAY to call a sql agent job using wild card either using CMD, power shell, batch file........etc.

• dan_27

  SSC Veteran

  Points: 259

  More actions

  July 27, 2018 at 9:49 am

  #1999267

  I want to create a store proc which execute the result of:
  Select name from sysjobs where name like 'myjobABC%'
  Another words just say the result of above is name = "myjobABC02018"
  then I want
  EXEC dbo.sp_start_job 'myjobABC02018'

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 27, 2018 at 9:55 am

  #1999268

  dan_27 - Friday, July 27, 2018 9:49 AM

  I want to create a store proc which execute the result of:
  Select name from sysjobs where name like 'myjobABC%'
  Another words just say the result of above is name = "myjobABC02018"
  then I want
  EXEC dbo.sp_start_job 'myjobABC02018'

  Yes, it is possible.  Depending on what you are trying to accomplish would determine how it is approached.  This could be done using a cursor for instance as you could get several job names returned by the query you posted.

• ZZartin

  SSC-Dedicated

  Points: 30898

  More actions

  July 27, 2018 at 10:17 am

  #1999273

  dan_27 - Friday, July 27, 2018 8:24 AM

  Thank for your reply. I got it "LIKE cannot be used as an assignment operator". Main point of my question is, is there a WAY to call a sql agent job using wild card either using CMD, power shell, batch file........etc.

  Yes, and this is actually one of the valid use cases for a cursor.

  DECLARE @job_name varchar(100)

  DECLARE job_cursor CURSOR FOR
SELECT name FROM msdb.dbo.sysjobs WHERE name LIKE 'Test_S%'
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job_name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @job_name
EXEC MSDB.dbo.sp_start_job @Job_Name = @job_name
FETCH NEXT FROM job_cursor INTO @job_name
END
CLOSE job_cursor
DEALLOCATE job_cursor

• ScottPletcher

  SSC Guru

  Points: 100950

  More actions

  July 27, 2018 at 10:24 am

  #1999277

  You don't really need a cursor for what you're described.

  
DECLARE @job_name_pattern nvarchar(128)
DECLARE @sql nvarchar(max)

  SET @job_name_pattern = 'MyJobEvery%'

  SELECT @sql = CAST((
  SELECT 'EXEC msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; '
  FROM msdb.dbo.sysjobs j
  WHERE j.name LIKE @job_name_pattern
  FOR XML PATH(''), TYPE
  ) AS nvarchar(max))

  PRINT @sql
EXEC(@sql)

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 27, 2018 at 10:50 am

  #1999283

  ScottPletcher - Friday, July 27, 2018 10:24 AM

  You don't really need a cursor for what you're described.

  
DECLARE @job_name_pattern nvarchar(128)
DECLARE @sql nvarchar(max)

  SET @job_name_pattern = 'MyJobEvery%'

  SELECT @sql = CAST((
  SELECT 'EXEC msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; '
  FROM msdb.dbo.sysjobs j
  WHERE j.name LIKE @job_name_pattern
  FOR XML PATH(''), TYPE
  ) AS nvarchar(max))

  PRINT @sql
EXEC(@sql)

  Maybe, maybe not.  Really depends on the use case doesn't.  For instance, since msdb.dbo.sp_start_job  is asynchronous, you may not want to start all the jobs that start with MyJobEvery all at once.

• ScottPletcher

  SSC Guru

  Points: 100950

  More actions

  July 27, 2018 at 11:11 am

  #1999286

  Lynn Pettis - Friday, July 27, 2018 10:50 AM

  ScottPletcher - Friday, July 27, 2018 10:24 AM

  You don't really need a cursor for what you're described.

  
DECLARE @job_name_pattern nvarchar(128)
DECLARE @sql nvarchar(max)

  SET @job_name_pattern = 'MyJobEvery%'

  SELECT @sql = CAST((
  SELECT 'EXEC msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; '
  FROM msdb.dbo.sysjobs j
  WHERE j.name LIKE @job_name_pattern
  FOR XML PATH(''), TYPE
  ) AS nvarchar(max))

  PRINT @sql
EXEC(@sql)

  Maybe, maybe not.  Really depends on the use case doesn't.  For instance, since msdb.dbo.sp_start_job  is asynchronous, you may not want to start all the jobs that start with MyJobEvery all at once.

  Then add a WAITFOR DELAY '00:00:0n.nnn' statement between each EXEC, like this:

  SELECT 'EXEC msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; WAITFOR DELAY ''00:00:02.000''; '

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  July 27, 2018 at 11:32 am

  #1999288

  ZZartin - Friday, July 27, 2018 10:17 AM

  dan_27 - Friday, July 27, 2018 8:24 AM

  Thank for your reply. I got it "LIKE cannot be used as an assignment operator". Main point of my question is, is there a WAY to call a sql agent job using wild card either using CMD, power shell, batch file........etc.

  Yes, and this is actually one of the valid use cases for a cursor.

  Yes a good bit of common sense. The fact that you might not want to start the jobs at the same time aside. You might save a hundredth of a second by not using a cursor but the jobs might take minutes to run so it wouldn't make much difference in the big scheme of things.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 27, 2018 at 12:08 pm

  #1999289

  ScottPletcher - Friday, July 27, 2018 11:11 AM

  Lynn Pettis - Friday, July 27, 2018 10:50 AM

  ScottPletcher - Friday, July 27, 2018 10:24 AM

  You don't really need a cursor for what you're described.

  
DECLARE @job_name_pattern nvarchar(128)
DECLARE @sql nvarchar(max)

  SET @job_name_pattern = 'MyJobEvery%'

  SELECT @sql = CAST((
  SELECT 'EXEC msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; '
  FROM msdb.dbo.sysjobs j
  WHERE j.name LIKE @job_name_pattern
  FOR XML PATH(''), TYPE
  ) AS nvarchar(max))

  PRINT @sql
EXEC(@sql)

  Maybe, maybe not.  Really depends on the use case doesn't.  For instance, since msdb.dbo.sp_start_job  is asynchronous, you may not want to start all the jobs that start with MyJobEvery all at once.

  Then add a WAITFOR DELAY '00:00:0n.nnn' statement between each EXEC, like this:

  SELECT 'EXEC msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; WAITFOR DELAY ''00:00:02.000''; '

  Going to continue being contrarian, and if that delay is wrong?  Sometimes a cursor is the right choice.  It is a tool and as a tool it can be used for good or evil.

• dan_27

  SSC Veteran

  Points: 259

  More actions

  July 27, 2018 at 12:17 pm

  #1999294

  Thanks ScottPletche and thanks all, it worked!!!!

• Tac11

  SSCertifiable

  Points: 6976

  More actions

  February 11, 2019 at 1:40 pm

  #2021711

  How can I execute the script from another server using 'Linked server'? I added 'linked server ' below after EXEC but not working!!! Another word I have a below sql agent job on SERVER1 but like to run from SERVER2 using exact same WILDCARD. 
  PS. I am NOT asking for like "Mylinkedserver.Adventureworks.person.person"

  DECLARE @job_name_pattern nvarchar(128)
  DECLARE @sql nvarchar(max)

  SET @job_name_pattern = 'MyJobEvery%'

  SELECT @sql = CAST((
  SELECT 'EXEC Mylinkedserver.msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; '
  FROM msdb.dbo.sysjobs j
  WHERE j.name LIKE @job_name_pattern
  FOR XML PATH(''), TYPE
  ) AS nvarchar(max))

  PRINT @sql
  EXEC(@sql)

• Michael L John

  One Orange Chip

  Points: 27043

  More actions

  February 11, 2019 at 2:13 pm

  #2021717

  Tac11 - Monday, February 11, 2019 1:40 PM

  How can I execute the script from another server using 'Linked server'? I added 'linked server ' below after EXEC but not working!!! Another word I have a below sql agent job on SERVER1 but like to run from SERVER2 using exact same WILDCARD. 
  PS. I am NOT asking for like "Mylinkedserver.Adventureworks.person.person"

  DECLARE @job_name_pattern nvarchar(128)
  DECLARE @sql nvarchar(max)

  SET @job_name_pattern = 'MyJobEvery%'

  SELECT @sql = CAST((
  SELECT 'EXEC Mylinkedserver.msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; '
  FROM msdb.dbo.sysjobs j
  WHERE j.name LIKE @job_name_pattern
  FOR XML PATH(''), TYPE
  ) AS nvarchar(max))

  PRINT @sql
  EXEC(@sql)

  You can't.  
  Try simply running EXEC Mylinkedserver.msdb.dbo.sp_start_job @Job_Name = 'YourJob'.  It will throw an error.

  You can create a procedure on the linked server, and pass in the name of the job.  In that proc you can call sp_start_job, and it should work.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• Jonathan AC Roberts

  SSCoach

  Points: 18271

  More actions

  February 11, 2019 at 2:28 pm

  #2021723

  Michael L John - Monday, February 11, 2019 2:13 PM

  Tac11 - Monday, February 11, 2019 1:40 PM

  How can I execute the script from another server using 'Linked server'? I added 'linked server ' below after EXEC but not working!!! Another word I have a below sql agent job on SERVER1 but like to run from SERVER2 using exact same WILDCARD. 
  PS. I am NOT asking for like "Mylinkedserver.Adventureworks.person.person"

  DECLARE @job_name_pattern nvarchar(128)
  DECLARE @sql nvarchar(max)

  SET @job_name_pattern = 'MyJobEvery%'

  SELECT @sql = CAST((
  SELECT 'EXEC Mylinkedserver.msdb.dbo.sp_start_job @Job_Name = ''' + j.name + '''; '
  FROM msdb.dbo.sysjobs j
  WHERE j.name LIKE @job_name_pattern
  FOR XML PATH(''), TYPE
  ) AS nvarchar(max))

  PRINT @sql
  EXEC(@sql)

  You can't.  
  Try simply running EXEC Mylinkedserver.msdb.dbo.sp_start_job @Job_Name = 'YourJob'.  It will throw an error.

  You can create a procedure on the linked server, and pass in the name of the job.  In that proc you can call sp_start_job, and it should work.

  You also would have needed to set up the linked server with RPC Out set to True: