I’ve seen this question asked a number of times over the past couple weeks and I thought I would share an approach using PowerShell and SMO. But first, let’s take a step back and try to understand why someone would want to do this. What’s wrong with SQL Agent?
Well there’s really nothing wrong with SQL Agent. The main reason I have seen people asking this question is because their company is looking into using an enterprise job scheduler. An enterprise job scheduler gives an operations group a single location to manage jobs across their whole environment. It allows them to see all the moving parts from a batch processing perspective, even across dissimilar platforms. It also allows them to create more complex workflows across multiple platforms.
So if a company is looking to switch to an enterprise job scheduling solution they will need a way to transition any of their existing SQL Agent jobs to run through the new scheduler. One way to accomplish this is to create a wrapper. A wrapper is nothing more than a script or program that calls another script or program, or in this case, a SQL Agent job.
USE [msdb]
GO
/****** Object: Job [TestJob] Script Date: 5/10/2015 9:06:29 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 5/10/2015 9:06:29 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'TestJob',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [TestStep] Script Date: 5/10/2015 9:06:30 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select @@servername
WAITFOR DELAY ''00:00:10''',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
function Start-SQLAgentJob
{
<#
.Notes
NAME: Start-SQLAgentJob
AUTHOR: Chris Sommer
Version: 1.0
CREATED: 2015-05-09
.Synopsis
Start a SQL Server Agent job.
.Description
Start a SQL Agent job and wait for its completion. This function relies on the SQL Agent to be up and running.
.Parameter SQLServer
SQL Server Name
.Parameter JobName
SQL Agent job name
.Example
Start-SQLAgentJob -SQLServer "localhost" -JobName "TestJob"
#>
[CmdletBinding()]
param (
[Parameter(Mandatory=$true)][string]$SQLServer ,
[Parameter(Mandatory=$true)][string]$JobName
)
# Load the SQLPS module
Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location
$ServerObj = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
$ServerObj.ConnectionContext.Connect()
$JobObj = $ServerObj.JobServer.Jobs | Where-Object {$_.Name -eq $JobName}
$JobObj.Refresh()
# If the job is and enabled and not currently executing start it
if ($JobObj.IsEnabled -and $JobObj.CurrentRunStatus -ne "Executing") {
$JobObj.Start()
}
# Wait until the job completes. Check every second.
do {
Start-Sleep -Seconds 1
# You have to run the refresh method to reread the status
$JobObj.Refresh()
} While ($JobObj.CurrentRunStatus -eq "Executing")
# Get the run duration by adding all of the step durations
$RunDuration = 0
foreach($JobStep in $JobObj.JobSteps) {
$RunDuration += $JobStep.LastRunDuration
}
$JobObj | select Name,CurrentRunStatus,LastRunOutcome,LastRunDate,@{Name="LastRunDurationSeconds";Expression={$RunDuration}}
}
# Setup pathing and environment based on the script location
$Invocation = (Get-Variable MyInvocation -Scope 0).Value
$ScriptLocation = Split-Path $Invocation.MyCommand.Path
# Load the Start-SQLAgentJob cmdlet
. "$ScriptLocation\Start-SQLAgentJob.ps1"
# Set a couple variables for testing and call the cmdlet
$SQLServer = "localhost\inst1"
$JobName = "TestJob"
Start-SQLAgentJob -SQLServer $SQLServer -JobName $JobName
And here are my results:
As always, Happy Scripting!