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
7 months 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: 1,671
    Helping Hand
    Rank: Community Hero

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

  • #108881

    Participant
    Points: 265
    Helping Hand
    Rank: Contributor

    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: 1,671
    Helping Hand
    Rank: Community Hero

    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: 265
    Helping Hand
    Rank: Contributor

    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: 1,671
    Helping Hand
    Rank: Community Hero

    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: 265
      Helping Hand
      Rank: Contributor

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

  • #109096

    Participant
    Points: 1,671
    Helping Hand
    Rank: Community Hero

    $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: 265
      Helping Hand
      Rank: Contributor

      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: 1,671
    Helping Hand
    Rank: Community Hero

    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: 1,671
    Helping Hand
    Rank: Community Hero

    seems like some genuine issue, try with different name.

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

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort