Start a SQL Agent Job with the .NET Framework from PowerShell

1Get-Command -Module SQLServer -Name *job* 

 1#Requires -Version 3.0  2function Start-MrSqlAgentJob {  3  4<#  5.SYNOPSIS  6 Starts the specified SQL Agent Job on the specified target instance of SQL Server.  7  8.DESCRIPTION  9 Start-MrSqlAgentJob is a PowerShell function that is designed to start the specified SQL Server  10 Agent job on the specified target instance of SQL Server without requiring the SQL Server PowerShell  11 module or snap-in to be installed.  12  13.PARAMETER ServerInstance  14 The name of an instance of SQL Server where the SQL Agent is running. For default instances, only  15 specify the computer name: MyComputer. For named instances, use the format ComputerName\InstanceName.  16  17.PARAMETER Name  18 Specifies the name of the Job object that this cmdlet gets. The name may or may not be  19 case-sensitive, depending on the collation of the SQL Server where the SQL Agent is running.  20  21.PARAMETER Credential  22 SQL Authentication userid and password in the form of a credential object.  23  24.EXAMPLE  25 Start-MrSqlAgentJob -ServerInstance SQLServer01 -Name syspolicy_purge_history  26  27.EXAMPLE  28 Start-MrSqlAgentJob -ServerInstance SQLServer01 -Name syspolicy_purge_history -Credential (Get-Credential)  29  30.EXAMPLE  31 'syspolicy_purge_history' | Start-MrSqlAgentJob -ServerInstance SQLServer01  32  33.INPUTS  34 String  35  36.OUTPUTS  37 Boolean  38  39.NOTES  40 Author: Mike F Robbins  41 Website: http://mikefrobbins.com  42 Twitter: @mikefrobbins  43#>  44  45 [CmdletBinding()]  46 param (  47 [Parameter(Mandatory)]  48 [string]$ServerInstance,  49  50 [Parameter(Mandatory,  51 ValueFromPipeLine)]  52 [string]$Name,  53  54 [System.Management.Automation.Credential()]$Credential = [System.Management.Automation.PSCredential]::Empty  55 )  56  57 BEGIN {  58  59 $Database = 'msdb'  60 $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection  61  62 if (-not($PSBoundParameters.Credential)) {  63 $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=True;"  64 }  65 else {  66 $connectionString = "Server=$ServerInstance;Database=$Database;Integrated Security=False;"  67 $userid = $Credential.UserName -replace '^.*\\|@.*$'  68 ($password = $credential.Password).MakeReadOnly()  69 $sqlCred = New-Object -TypeName System.Data.SqlClient.SqlCredential($userid, $password)  70 $connection.Credential = $sqlCred  71 }  72  73 $connection.ConnectionString = $connectionString  74 $ErrorActionPreference = 'Stop'  75  76 try {  77 $connection.Open()  78 Write-Verbose -Message "Connection to the $($connection.Database) database on $($connection.DataSource) has been successfully opened."  79 }  80 catch {  81 Write-Error -Message "An error has occurred. Error details: $($_.Exception.Message)"  82 }  83  84 $ErrorActionPreference = 'Continue'  85 $command = $connection.CreateCommand()  86  87 }  88  89 PROCESS {  90  91 $Query = "EXEC dbo.sp_start_job N'$Name'"  92 $command.CommandText = $Query  93 $ErrorActionPreference = 'Stop'  94  95 try {  96 $result = $command.ExecuteNonQuery()  97 }  98 catch {  99 Write-Error -Message "An error has occured. Error Details: $($_.Exception.Message)" 100 } 101 102 $ErrorActionPreference = 'Continue' 103 104 if ($result -eq -1) { 105 Write-Output $true 106 } 107 else { 108 Write-Output $false 109 } 110 111 } 112 113 END { 114 115 $connection.Close() 116 $connection.Dispose() 117 118 } 119 120} 

1Start-MrSqlAgentJob -ServerInstance SQL011 -Name syspolicy_purge_history 

1Get-SqlAgentJob -ServerInstance SQL011 -Name test | Get-Member -MemberType Method 

1(Get-SqlAgentJob -ServerInstance SQL011 -Name test).Start() 2Get-SqlAgentJob -ServerInstance SQL011 -Name test