Author Posts

August 17, 2018 at 1:57 pm

I am writing a script, to keep SQL Agent jobs in sync, in an Availability Group in SQL Server. When I find a job that has different step names, I try to update the names on the secondary servers to keep up with changes in the Primary jobs. Here is the snippet where I try to rename the secondary job step:

$sJobStep.Rename($pJobStep.Name)

$sJobStep.Alter()

Where $sJobStep is the secondary job and $pJobStep is from the Primary job.

Here is the error I am getting:

SECONDARY JobStep Name is: step 5

Exception calling "Rename" with "1" argument(s): "Rename failed for JobStep 'step

5'. "

At I:\Scripts\Sync Job Steps – V9.ps1:138 char:49

+ ...                                      $sJobStep.Rename($pJobStep.Name)

+                                          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

+ FullyQualifiedErrorId : FailedOperationException

 

I have tried researching the JobStep.Rename function, but I can't find any help with this error. Can someone point me in the right direction?

August 17, 2018 at 2:19 pm

You have to use gist (via github) or the 'pre' tags for posting script in the forum.

August 17, 2018 at 3:25 pm

OK, tried and tested.....


$SqlServer = "server\instance"
$Job = "jobname"
$Step = "stepname." # ID 2
$newname = "readbeforeanswering"

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = new-object Microsoft.SqlServer.Management.Smo.Server $SqlServer  
$Srv.JobServer.Jobs[$Job].JobSteps[$Step].Rename($newname)
$Srv.JobServer.Jobs[$Job].JobSteps[$newname].Refresh()

August 20, 2018 at 4:17 pm


I tried your code. i still get the error. I made a function out of your script:

function RenameJobStep
{
param   (
[CmdletBinding()]
[Parameter(ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true)]
[string[]]$SqlServer,
[string[]]$Job,
[string[]]$Step,
[string[]]$newname
)

 

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = new-object Microsoft.SqlServer.Management.Smo.Server $SqlServer
$Srv.JobServer.Jobs[$Job].JobSteps[$Step].Rename($newname)
$Srv.JobServer.Jobs[$Job].JobSteps[$newname].Refresh()
}

 

I still get this error:
Method invocation failed because [System.Object[]] does not contain a method named
'Rename'.
At line:1 char:9
+         $Srv.JobServer.Jobs[$Job].JobSteps[$Step].Rename($newname)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

August 20, 2018 at 4:34 pm

I'm not able to repro this.

How you are invoking the function ? what are the values given to $Job and $Step. Those are indices.

Debug your code in an editor or check the output step by step by printing below values.

$Srv.JobServer.Jobs[$Job]
$Srv.JobServer.Jobs[$Job].JobSteps
$Srv.JobServer.Jobs[$Job].JobSteps[$Step]

August 20, 2018 at 4:56 pm

Here is how I can calling the function:

SqlServer = $SecondaryServer
#Job = $sJob.Name
$Step = $pJobStep.Name
$newname = $pJobStep.Name

RenameJobStep $SqlServer $Job $Step $newname

If you want my full script, let me know!

August 20, 2018 at 5:02 pm

Here is the result from your question:

Name
—-
[Add Step Job2]
New Step 1 Name
step 2 name
step 3 Name

August 20, 2018 at 5:13 pm

Clifton,
I was able to reproduce the problem but I have to admit, right now, I don't fully understand what the issue is. Hopefully one of our other Powershell folk will be able to explain the cause. I do say you've got "[CmdletBinding()]" in the wrong place, but even correcting that doesn't let it succeed.
What I see is that if I create the Function like this, see below, it's ok. But when I add in the value from pipeline etc, the error occurs.

Function Rename-AgentStep
{
    [CmdletBinding()]
    param (
    [string]$SqlServer ,
    [string]$Job ,
    [string]$Step ,
    [string]$newname 
    )

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = new-object Microsoft.SqlServer.Management.Smo.Server $SqlServer  
$Srv.JobServer.Jobs[$Job].JobSteps[$Step].Rename($newname)
$Srv.JobServer.Jobs[$Job].JobSteps[$newname].Refresh()

}

August 20, 2018 at 8:06 pm

If $Job is the name of the job and $Step is the name of the JobStep, you can filter the output to get the proper object. [] on an object is used to mention index for cherry pinking values as we do in an array.

$JobFilteredByName = $Srv.JobServer.Jobs | Where-Object -FilterScript { $_.Name -eq $Job }
$JobFilteredByStepName = $JobFilteredByName.JobSteps | Where-Object -FilterScript { $_.Name -eq $Step }
$JobFilteredByStepName.Rename($NewName)

August 20, 2018 at 8:10 pm

How is that related to the problem encountered when the parameter attributes are defined ?
*Not contradicting you, just not understanding.

August 20, 2018 at 8:24 pm

$Srv.JobServer.Jobs returns collection of jobs, so when you do $Srv.JobServer.Jobs[$Job], $Job should be an integer and will be taken as index to pick the item from the collection.
But from your reply I understood that $Job is not an index but Name of the job, hence you have to filter the output based on the name.

August 24, 2018 at 5:01 pm

I have tried the latest version of this but, still get errors:

$JobFilteredByStepName.Rename($NewName)
Exception calling "Rename" with "1" argument(s): "Rename failed for JobStep 'Erase
Phantom System Health Records.'. "
At line:1 char:25
+ $JobFilteredByStepName.Rename($NewName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException

August 24, 2018 at 5:11 pm

This is my latest function that is still getting errors:

function RenameJobStep

{

param   (

[string[]]$SqlServer,

[string[]]$Job,

[string[]]$Step,

[string[]]$NewName

)

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$srv = new-object Microsoft.SqlServer.Management.Smo.Server $SqlServer

$JobFilteredByName = $Srv.JobServer.Jobs | Where-Object -FilterScript { $_.Name -eq $Job }

$JobFilteredByStepName = $JobFilteredByName.JobSteps | Where-Object -FilterScript { $_.Name -eq $Step }

if ($JobFilteredByStepName -eq $null)

{

Write-Host "Something went wrong finding Job: "$JobFilteredByName -BackgroundColor Red

}

else

{

Write-Host $JobFilteredByStepName

try

{

#Wait 5 seconds to see if this delay will fix the issue

Wait-Event -Timeout 5

$JobFilteredByStepName.Rename($NewName)

}

catch

{

#write-host $Error

$ErrorMessage = $_.Exception.Message

$FailedItem = $ErrorMessage

Write-Host $ErrorMessage

Write-Host $FailedItem

Write-Host "Failed on Job: "$Job

Write-Host "Failed on Step: "$Step

Write-Host "Failed on Server: "$SqlServer

Write-Host "Failed on New Step Name: "$NewName

Write-Host `n

 

}

}

}

August 24, 2018 at 5:17 pm

I think, its because of the datatype, rename takes string not string array. Parameter $NewName is defined as String array ([string][]) in the function.

Note: Please use code posting tags for posting code.

August 24, 2018 at 5:43 pm

I changed the variables to just plain string, but that did not help:


function RenameJobStep
{
param   (
[string]$SqlServer,
[string]$Job,
[string]$Step,
[string]$NewName
)
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$srv = new-object Microsoft.SqlServer.Management.Smo.Server $SqlServer
$JobFilteredByName = $Srv.JobServer.Jobs | Where-Object -FilterScript { $_.Name -eq $Job }
$JobFilteredByStepName = $JobFilteredByName.JobSteps | Where-Object -FilterScript { $_.Name -eq $Step }
if ($JobFilteredByStepName -eq $null)
{
Write-Host "Something went wrong finding Job: "$JobFilteredByName -BackgroundColor Red
}
else
{
Write-Host $JobFilteredByStepName
try
{
$JobFilteredByStepName.Rename($NewName)
}
catch
{
#write-host $Error
$ErrorMessage = $_.Exception.Message
$FailedItem = $ErrorMessage
Write-Host $ErrorMessage
Write-Host $FailedItem
Write-Host "Failed on Job: "$Job
Write-Host "Failed on Step: "$Step
Write-Host "Failed on Server: "$SqlServer
Write-Host "Failed on New Step Name: "$NewName
Write-Host `n

}

}

}

 

I still get the same error:

Exception calling "Rename" with "1" argument(s): "Rename failed for JobStep 'Erase
Phantom System Health Records.'. "
At I:\Scripts\Sync Job Steps – V9.ps1:112 char:25
+ $JobFilteredByStepName.Rename($NewName)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : FailedOperationException

August 24, 2018 at 7:12 pm

seems like some genuine issue, try with different name.

August 24, 2018 at 7:39 pm

The script i posted works fine in all my tests....