Create SQL Agent job using SMO


# Powershell script to create a new Agent job and list the job using JobCollection

# Inspects error collection and prints all inner exception
function CheckForErrors
{
        $errorsReported = $False

        if($Error.Count ne 0)
        {
                write-host “******************************”
                write-host “Errors:”, $Error.Count
                write-host “******************************”
                foreach($err in $Error)
                {
                        $errorsReported  = $True
                        if( $err.Exception.InnerException ne $null)
                        {
                                write-host $err.Exception.InnerException.ToString()
                        }
                        else
                        {
                                write-host $err.Exception.ToString()
                        }
                        write-host “———————————————-“
                }
                throw
        }
}

# Create a new job and get job back from the job collection
function CreateSqlAgentJobAndVerify
{
        Param([string]$serverName)
       
        #clear all previous errors
        $Error.Clear()
       
        write-host “————————————————————-“
        write-host ” Connecting to server:” $serverName ” (using SMO)”
        write-host “————————————————————-“
        $smoServer= new-object(‘Microsoft.SqlServer.Management.Smo.Server’)  $serverName
       
        $jobName = ‘TestJob’  + (new-object Random).Next().Tostring()
        write-host “————————————————————-“
        write-host ” Creating new job:”  $jobName  ” on Server:”  $smoServer.Name
        write-host “————————————————————-“
       
        #create a new job
        $job = new-object (‘Microsoft.SqlServer.Management.Smo.Agent.Job’) ($smoServer.JobServer, $jobName)
        $job.Create()
        CheckForErrors
       
        # list newly created job
        write-host “————————————————————-“
        write-host ” Listing newly created job…”
        write-host ” Job:” $jobName ” on Server:”  $smoServer.Name
        write-host “————————————————————-“
        $smoServer.JobServer.Jobs[$jobName]
        CheckForErrors
}

# replace server name with your fully qualified instance name
$serverName = “.”
CreateSqlAgentJobAndVerify $serverName

# 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

Comments (1)

  1. kapil says:

    Thank You

    The given information is very effective

    i will keep updated with the same

    <a href ="http://www.sweetball.in">seo training in noida </a>