Issues with SMO renaming Job Steps

Welcome Forums General PowerShell Q&A Issues with SMO renaming Job Steps

This topic contains 16 replies, has 3 voices, and was last updated by

 
Participant
3 months, 3 weeks ago.

  • Author
    Posts
  • #108854

    Participant
    Points: 1
    Rank: Member

    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?

  • #108875

    Participant
    Points: 894
    Helping Hand
    Rank: Major Contributor

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

  • #108881

    Participant
    Points: 123
    Rank: Participant

    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()
    
    
    • #109046

      Participant
      Points: 1
      Rank: Member
      
      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
  • #109048

    Participant
    Points: 894
    Helping Hand
    Rank: Major Contributor

    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]
    
    • #109060

      Participant
      Points: 1
      Rank: Member

      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!

    • #109061

      Participant
      Points: 1
      Rank: Member

      Here is the result from your question:

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

  • #109063

    Participant
    Points: 123
    Rank: Participant

    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()
    
    }
    
  • #109091

    Participant
    Points: 894
    Helping Hand
    Rank: Major Contributor

    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)
    
    • #109093

      Participant
      Points: 123
      Rank: Participant

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

  • #109096

    Participant
    Points: 894
    Helping Hand
    Rank: Major Contributor

    $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.

    • #109928

      Participant
      Points: 123
      Rank: Participant

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

  • #109882

    Participant
    Points: 1
    Rank: Member

    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

  • #109883

    Participant
    Points: 1
    Rank: Member

    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

     

    }

    }

    }

  • #109885

    Participant
    Points: 894
    Helping Hand
    Rank: Major Contributor

    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.

  • #109892

    Participant
    Points: 1
    Rank: Member

    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

  • #109922

    Participant
    Points: 894
    Helping Hand
    Rank: Major Contributor

    seems like some genuine issue, try with different name.

The topic ‘Issues with SMO renaming Job Steps’ is closed to new replies.