PowerShell Script to Start SQL Job

Following Uses Windows Authentication to connect to the SQL Server called “(Local)” and Start the SQL Job called “test”.

It will throw exception if servername is incorrect, or Job name is incorrect, or if you are trying to start a job that is already running :
e.g. “SQLServerAgent Error: Request to run job test (from User MyDomain\MyUserName) refused because the job is already running from a request by User MyDomain\MyUserName.”

$serverName = “(Local)”
$jobname = “test”

[void][System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)| Out-Null
Try{
$server = new-object “Microsoft.SqlServer.Management.Smo.Server” $serverName
$server.ConnectionContext.ConnectTimeout=1
$job = $server.JobServer.Jobs[$jobname]
}Finally{
if ($server.JobServer.Name -eq $null){ throw “Error: [“+$serverName.toUpper()+”] SQL Server does not Exist.”; }
elseif ($job.Name -eq $null){ throw “Error: [“+$serverName.toUpper()+”].Jobs.[“+ $jobname +”] does not Exist.” }
else{try {$job.Start();}
catch {throw $_.Exception.GetBaseException().Message;}
}
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s