Creating Agent Job and Steps having error

PowerShell Help
1

Hello,

I’m fairly new to powershell with sql

Was trying to follow a script I found, but It has some errors when I’m running. Can you help me see what I did wrong? If you also have another way of creating Agent Job and Adding steps in agent job, it will be much appreciated

## Create the CreateAgentJob Function
function createAgentJob($jobName, $serverName, $owner)
{
# Create the job
$server = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($serverName)
$sqlJob = $server.JobServer.Jobs[$jobName]

if ($sqlJob)
{
$sqlJob.Rename("z_"+$sqlJob.Name +"_OLD_" + (Get-Date -f MM-dd-yyyy_HH_mm_ss))
$sqlJob.IsEnabled = $false
$sqlJob.Alter()
}

$sqlJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $server.JobServer, $jobName
$sqlJob.OwnerLoginName = $owner
$sqlJob.Create()

$sqlJob.ApplyToTargetServer($serverName)
$sqlJob.Alter()
}

## Set strings
$jobNameString = "TestScheduledRestoreBackup"
$serverNameString = "(localdb)\MSSQLLocalDB"
$ownerString = "DEV\gee"

## Runs CreateAgentJob
createAgentJob -jobName $jobNameString -serverName $serverNameString -owner $ownerString

The following are the errors

 

Cannot index into a null array.
At line:6 char:5

  • $sqlJob = $server.JobServer.Jobs[$jobName]
  • CategoryInfo : InvalidOperation: (:slight_smile: , RuntimeException
  • FullyQualifiedErrorId : NullArray

New-Object : Exception calling “.ctor” with “2” argument(s): "SetParent failed for Job ‘TestScheduledRestoreBackup’. "
At line:15 char:15

  • $sqlJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job …
  • CategoryInfo : InvalidOperation: (:slight_smile: [New-Object], MethodInvocationException
  • FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

The property ‘OwnerLoginName’ cannot be found on this object. Verify that the property exists and can be set.
At line:16 char:5

  • $sqlJob.OwnerLoginName = $owner
  • CategoryInfo : InvalidOperation: (:slight_smile: , RuntimeException
  • FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At line:17 char:5

  • $sqlJob.Create()
  • CategoryInfo : InvalidOperation: (:slight_smile: , RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:19 char:5

  • $sqlJob.ApplyToTargetServer($serverName)
  • CategoryInfo : InvalidOperation: (:slight_smile: , RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:20 char:5

  • $sqlJob.Alter()
  • CategoryInfo : InvalidOperation: (:slight_smile: , RuntimeException
  • FullyQualifiedErrorId : InvokeMethodOnNull

PS C:>

2

You could better format you code to make It more readable. You could use https://gist.github.com/ to post the code snippet here.

3

Worked fine for me, only differences between what you posted and what I ran was the inclusion of



[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null;
...and I supplied the full name of the Sql Server Instance. ie: SqlServer\Instance
4

[quote quote=138354]You could better format you code to make It more readable. You could use https://gist.github.com/ to post the code snippet here.

[/quote]

Oh thanks, will take note on that