Microsoft SQL Server on Windows Azure Virtual Machines

The writers of this blog are a part of the Microsoft SQL Server team. We will be blogging about our tools, scripts, webcasts, and miscellaneous tips that will help you get the most out of SQL Server components on Windows Azure Virtual machines

Starting Jobs in Powershell using Job Name

Starting Jobs in Powershell using Job Name

  • Comments 3

 

When starting jobs in powershell with smo, you have to use the jobname and categoryID. If you have same job name on multiple servers that could be in different category, and if you are running a single script that connects to multiple servers and starts job, it will not work because JobName+CategoryID combination might not be unique on all servers

This issue is due to the fact that JobsCollection in SMO has JobName + CategoryID as composite unique key. We have the same constraint in sysjobs table in msdb too. JobID is unique key. You could use JobID to query a specific job. But JobID may not be same on all servers that have jobs created with same name.

Workaround for this problem is to do the following

1) Enumerate SMO JobsCollection and filter jobs by given Name

2) for each result from #1, get specific JobID

3) use JobServer SMO object's GetJobByID() method to get the corresponding SMO job instance

4) use the retrieved SMO Job instance in #3 to Start a job

 

   1: PowerShellCode:
   2: Here is the powershell code to start a job by given job name:
   3: # Note: in SQLPS window, CD SQLSERVER:\SQL\<Your_server>\<Inatance_Namw>\JobServer\Jobs
   4: $jobName = 'mytestjob'
   5:  
   6: # get all jobs with specified name
   7: $jobsByName = (get-item .).Collection | where-object { $_.Name -eq $jobName }
   8: foreach($job in $jobsByName) 
   9: { 
  10:     # for corresponding job ID, get job object reference and Start the job
  11:     (get-item ..).GetJobByID($job.JobId).Start() 
  12: }

 

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post
  • Ouch.  While this is useful to know, I would think having it default to a category of '%' and having it do a like match would be better.  It seems like SMO really has some rough edges when it comes to PowerShell.  I think MS needs to really do some usability studies involving SQL Server and PowerShell.  Right now it isn't very usable, it seems like you need to make scripts for everything to get it to work as expected.  As a little example try to write a single line in PowerShell that will script all objects in a database, regardless of object type, for SQL 2005, 2008, and 2008 R2.  Since you get errors on objects categories not existing for certain versions, it's a real pain, but it shouldn't be.

    Heck, the SQL team should provide cmdlets to do all this.  Not just a simple thin layer on top of SMO.  The following is how your example would work if you had well written cmdlets:

    Start-SQLJob -Server 'SQLServer' -JobName 'MyTestJob'

    or to start it by jobid:

    Get-SQLJob -Server 'SQLServer' -JobName 'MyTestJob' | Start-SQLJob

    Just by looking at these examples, you can see what they are doing. In the second example, the Get-SQLJob would return the name of the job along with the category, Start-SQLJob pulls the Server and the JobID from the pipeline.  You don't need to do any odd looking commands or scripting work arounds to get it to work.

  • Thanks for your valuable feedback Stephen.

    Agree with you on delivering cmdlets for SQL agent that would simplify tasks. Could you please post your feedback and proposed suggestion at http://connect.microsoft.com ?. Our dev team can look at addressing this issue in future releases of SQL Server

  • This also works:

    # Note: in SQLPS window, CD SQLSERVER:\SQL\<Your_server>\<Inatance_Namw> $jobName = 'mytestjob'

    $jobserver = get-item JobServer

    ($jobserver.Jobs["$jobName"]).Start()

Page 1 of 1 (3 items)