enum failing when used as a type in param()

Welcome Forums General PowerShell Q&A enum failing when used as a type in param()

Viewing 14 reply threads
  • Author
    Posts
    • #241607
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      I’ve a Function that I use an enum to help check values passed to one of the parameters.

      enum allowedsubsystem
      {
      activescripting
      analysiscommand
      analysisquery
      cmdexec
      distribution
      logreader
      merge
      powershell
      queuereader
      snapshot
      ssis
      transactsql
      }
      
      

      I’ve declared the parameter in the function like this:

      [Parameter(Mandatory=$False)]
      [allowedsubsystem]$SubSystem
      
      

      The function is called like this:

      $Params = @{
      SqlServer = 'D01\DEV'
      RetryAttempts = '1'
      OnSuccessAction = 'GoToNextStep'
      JobStepName = 'devtesttest - Exec Daily Master DW ETL - IsDailyLoad False'
      OnFailAction = 'GoToNextStep'
      Command = '/ISSERVER "\"\SSISDB\DW\Data Warehouse ETL\DW Master ETL.dtsx\"" /SERVER "\"D01\DEV\"" /Par "\"$Project::DWDatabaseConnectionString\"";"\"Data Source=SQLD01\DEV01;Initial Catalog=reporting_devaca1;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /Par "\"$Project::IsDailyLoad(Boolean)\"";False /Par "\"$Project::IsStandardEdition(Boolean)\"";False /Par "\"$Project::SourceDatabaseConnectionString\"";"\"Data Source=SQLVD01\DEV01;Initial Catalog=devtesttest;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";2 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
      SubSystem = 'ssis'
      Databasename = 'ETLD01\DEV'
      JobName = 'DW - SSIS_Daily Master DW ETL - Run All In batches - STEP 2 - Cornerstone - D01 - 1'
      RetryInterval = '10'
      }
      Add-SqlAgentJobStep @Params
      
      

      But when I run it, I get this error, see below, if I remove the enum and set the parameter to string, it works ok.

      What am I doing wrong?

      Exception calling "Create" with "0" argument(s): "Create failed for JobStep 'devtesttest -
      Exec Daily Master DW ETL - IsDailyLoad False'. " ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Create failed for JobStep
      'csdevtesttest - Exec Daily Master DW ETL - IsDailyLoad False'. ---> Microsoft.SqlServer.Management.Smo.PropertyNotSetException: To accomplish this
      action, set property Server.
      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValue(String propName)
      at Microsoft.SqlServer.Management.Smo.Agent.JobStep.GetAllParams(StringBuilder sb, ScriptingPreferences sp, Int32& count)
      at Microsoft.SqlServer.Management.Smo.Agent.JobStep.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean
      skipPropagateScript)
      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
      --- End of inner exception stack trace ---
      at Microsoft.SqlServer.Management.Smo.SqlSmoObject.CreateImpl()
      at CallSite.Target(Closure , CallSite , Object )
      --- End of inner exception stack trace ---
      at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
      at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
      at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
      at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
      TargetObject :
      CategoryInfo : NotSpecified: (:) [], MethodInvocationException
      FullyQualifiedErrorId : FailedOperationException
      ErrorDetails :
      InvocationInfo : System.Management.Automation.InvocationInfo
      ScriptStackTrace : at Add-SqlAgentJobStep<Process>, <No file>: line 181
      at <ScriptBlock>, <No file>: line 13
      PipelineIterationInfo : {}
      PSMessageDetails :
      
      

       

    • #241613
      Senior Moderator
      Topics: 9
      Replies: 1264
      Points: 4,576
      Helping Hand
      Rank: Community Hero

      I am suspecting a type related issue. Can you try converting $subsystem to string as the passed value will be type casted to allowedsubsystem type the function which could be something not supported by the cmdlet that you are using. So you can try converting it to string.

      $SubSystemString = $SubSystem.ToString()
      

      And then use $SubSystemString while calling the actual cmdlet.

    • #241616
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      I did this, see below, but still failed…..same error.

      [string]$SubSystem = 'ssis'
      Add-SqlAgentJobStep @Params -SubSystem $SubSystem
      
      
    • #241808
      Moderator
      Topics: 4
      Replies: 234
      Points: 1,276
      Helping Hand
      Rank: Community Hero
      Command = '/ISSERVER "\"\SSISDB\DW\Data Warehouse ETL\DW Master ETL.dtsx\"" /SERVER "\"D01\DEV\"" /Par "\"$Project::DWDatabaseConnectionString\"";"\"Data Source=SQLD01\DEV01;Initial Catalog=reporting_devaca1;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /Par "\"$Project::IsDailyLoad(Boolean)\"";False /Par "\"$Project::IsStandardEdition(Boolean)\"";False /Par "\"$Project::SourceDatabaseConnectionString\"";"\"Data Source=SQLVD01\DEV01;Initial Catalog=devtesttest;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";2 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
      
      Try running the command directly and see the results.
      
      Also, try using the ValidateSet or ArgumentCompleter attributes instead is enum.
      
      Thank you.
      
      
      
    • #241841
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      The command works ok, I’ve shown that by removing the enum from the paramter declaration.

      This works

      
      
      [string]$SubSystem
      

      This fails

      
      
      [allowedsubsystem]$SubSystem
      

      The enum used is:

      enum allowedsubsystem {
          activescripting	
          analysiscommand	
          analysisquery
          cmdexec	
          distribution
          logreader
          merge	
          powershell
          queuereader	
          snapshot	
          ssis
          transactsql
      }
      

      I have the parameters declared like this:

      [CmdletBinding()]
          param (
          [parameter(Mandatory=$true)]
          [ValidateNotNullOrEmpty()]
          [string]$SqlServer ,
          [parameter(Mandatory=$true)]
          [ValidateNotNullOrEmpty()]
          [string]$JobName ,
          [parameter(Mandatory=$true)]
          [ValidateNotNullOrEmpty()]
          [string]$JobStepName,
          [Parameter(Mandatory=$False)]
          [int]$StepID ,
          [Parameter(Mandatory=$False)]
          [string]$Command ,
          [Parameter(Mandatory=$False)]
          [string]$DatabaseName ,
          [Parameter(Mandatory=$False)]
          [allowedsubsystem]$SubSystem , 
          [Parameter(Mandatory=$False)]
          [string]$OnSuccessAction ,
          [Parameter(Mandatory=$False)]
          [string]$OnFailAction ,
          [Parameter(Mandatory=$False)]
          [string]$RetryAttempts ,
          [Parameter(Mandatory=$False)]
          [string]$RetryInterval 
          )
      
    • #241862
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      @ Kiran,

      I’ve not used ValidateSet in the parameter declaration, as i don’t want to have to put all these options in every Function. That, if I understand correctly, is what enum is for, so I can declare these options once, and there after refer to it in functions as required.

      The option of using ArgumentCompleter is not an option in this case, if I understand correctly what it’s for and how it works.

    • #241865
      Moderator
      Topics: 4
      Replies: 234
      Points: 1,276
      Helping Hand
      Rank: Community Hero

      You can also use the ValidateSet attribute like this as well…

      class SupportedCountries : System.Management.Automation.IValidateSetValuesGenerator
      {
      [string[]] GetValidValues()
      {
      ## Write your code here
      $Countries = @(‘India’, ‘USA’, ‘UK’, ‘Canada’, ‘Australia’)
      return $Countries
      }
      }
      
      Function Get-Country
      {
      [CmdLetBinding()]
      param
      (
      [parameter(Mandatory = $true)]
      [ValidateSet([SupportedCountries])]
      [string] $CountryName
      )
      
      ## Write your code here
      Write-Host $CountryName
      }
    • #241919
      Senior Moderator
      Topics: 9
      Replies: 1264
      Points: 4,576
      Helping Hand
      Rank: Community Hero

      Whit I meant is to receive the input as enum then convert it to string before passing to actual cmdlet.

      Can you post the line 181 as mentioned by the stacktrace

      ScriptStackTrace : at Add-SqlAgentJobStep<Process>, <No file>: line 181
      
    • #241928
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      @ Kiran,

      Apparently ‘System.Management.Automation.IValidateSetValuesGenerator’ is only available in PS 6+

      https://docs.microsoft.com/en-us/dotnet/api/system.management.automation.ivalidatesetvaluesgenerator?view=pscore-6.0.0
      
      
    • #241931
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      @ Kvprasoon

      Line 181 in the try in the try .. catch that holds the create

      try { # <---line 181
      $NewStep.Create()
      } catch {
      $_.Exception.Message
      $_.Exception.ItemName
      Break
      }
      
      

      This is the process step of the function

      $SQLSvr = New-Object Microsoft.SQLServer.Management.Smo.Server $SqlServer
      $JobServer = $SQLSvr.JobServer
      $Job = $JobServer.Jobs[$JobName]
      
      $NewStep = New-Object Microsoft.SqlServer.Management.SMO.Agent.JobStep
      $NewStep.Name = $JobStepName
      $NewStep.Parent = $Job
      $NewStep.Command = $Command
      $NewStep.SubSystem = $SubSystem
      if($SubSystem -eq 'ssis'){$NewStep.DatabaseName = $DatabaseName}
      $NewStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::$OnSuccessAction
      $NewStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::$OnFailAction
      $NewStep.RetryAttempts = $RetryAttempts
      $NewStep.RetryInterval = $RetryInterval
      if($StepID){$NewStep.ID = $StepID}
      #$SQLJobStep.DatabaseName = $SsisServer
      
      try {
      $NewStep.Create()
      } catch {
      $_.Exception.Message
      $_.Exception.ItemName
      Break
      }
      
      

       

    • #242018
      Senior Moderator
      Topics: 9
      Replies: 1264
      Points: 4,576
      Helping Hand
      Rank: Community Hero

      Thanks for sharing the snippet, so what I was trying to convey is to do the below.

      $NewStep.SubSystem = $SubSystem.ToString()
      
    • #242099
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      Yes, got that, and tried it, and it failed with the same error.

    • #242102
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      @ kvprasoon,

      Not sure what happened the first time I attempted that but I’ve retired it multiple times now and each time it’s worked and created the step.

      Nice to get it to working, but it’d be great for me to understand why this was an issue…? The parameter was accepted fine without the enum being used.

      $NewStep = New-Object Microsoft.SqlServer.Management.SMO.Agent.JobStep
      $NewStep.Name = $JobStepName
      $NewStep.Parent = $Job
      $NewStep.Command = $Command
      $NewStep.SubSystem = $SubSystem.ToString()
      if($SubSystem -eq 'ssis'){$NewStep.DatabaseName = $DatabaseName}
      $NewStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::$OnSuccessAction
      $NewStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::$OnFailAction
      $NewStep.RetryAttempts = $RetryAttempts
      $NewStep.RetryInterval = $RetryInterval
      if($StepID){$NewStep.ID = $StepID}
      
      try {
      $NewStep.Create()
      } catch {
      $_.Exception.Message
      $_.Exception.ItemName
      Break
      }
      
      
    • #242198
      Senior Moderator
      Topics: 9
      Replies: 1264
      Points: 4,576
      Helping Hand
      Rank: Community Hero

      Form what I understood, $NewStep.SubSystem requires the type to be specific and can only type cast from a string type, so custom enum type cannot be converted to the expected Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem type.

    • #242309
      Participant
      Topics: 50
      Replies: 206
      Points: 838
      Helping Hand
      Rank: Major Contributor

      Thanks!

      I appreciate the help and the info.

Viewing 14 reply threads
  • You must be logged in to reply to this topic.