Pipeline multiple parameters fails

Welcome Forums General PowerShell Q&A Pipeline multiple parameters fails

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

 
Participant
1 week, 6 days ago.

  • Author
    Posts
  • #113417

    Participant
    Points: 3
    Rank: Member

    I'm trying to create a Function,  that will accept multiple parameters from the pipeline. The idea is to provide a list of servers and databases and ensure each database is set to Simple.

    Function Set-RecoveryModel
    
    {
    
    [CmdletBinding()]
    
    param (
    
    [Parameter(Mandatory=$true, ValuefromPipelineByPropertyName=$true,Position=0)]
    
    [Alias('IPAddress','CN')]
    
    [string]$SqlServer ,
    
    [Parameter(Mandatory=$true, ValuefromPipelineByPropertyName=$true,Position=1)]
    
    [string]$Database ,
    
    [Parameter(Mandatory=$false, ValuefromPipelineByPropertyName=$true,Position=2)]
    
    [string]$Recovery = "Simple"
    
    )
    
    BEGIN {
    
    Write-Verbose $SqlServer
    
    Write-Verbose $Database
    
    Write-Verbose $Recovery
    
    Write-Verbose "Loading Module"
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    
    $Srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SqlServer
    
    }
    
    PROCESS {
    
    Write-Verbose "Setting recovery"
    
    $Srv.Databases[$Database].RecoveryModel = $Recovery
    
    Try {
    
    $Srv.Databases[$Database].Alter()
    
    } catch {
    
    Write-Verbose "Error: $($_.Exception.Message)`r`n$($_.Exception.ItemName)"
    
    Continue
    
    }
    
    $Srv.Databases[$Database].Rfresh
    
    }
    
    END {
    
    Write-Verbose "All Done"
    
    }
    
    }
    
    

    When I run it passing the parameter values directly, it works as expected.

    $SqlServer = "SqlServer\DEV"
    
    $Database = "ContosoRetailDW"
    
    $Recovery = "Simple"
    
    Set-RecoveryModel -SqlServer $SqlServer -Database $Database -Recovery $Recovery
    
    

    But when I try and pipe the parameter values, it errors.

    $DbInfo = @{
    
    SqlServer = 'SqlServer\DEV'
    
    Database = 'ContosoRetailDW'
    
    Recovery = 'Simple'
    
    }
    
    [PSCustomObject]$DbInfo | Set-RecoveryModel -Verbose
    
    

    ...and

    @([pscustomobject]@{
    
    SqlServer = "SqlServer\DEV";
    
    Database = "ContosoRetailDW";
    
    Recovery = "Simple";},
    
    [pscustomobject]@{
    
    SqlServer = "SqlServer\DEV";
    
    Database = "DBARepository";
    
    Recovery = "Simple";}) | Set-RecoveryModel
    
    

     

    I can see that the parameter values I'm passing are not being passed to the Function but I don't know why.

    Both return the same error

    VERBOSE:
    
    VERBOSE:
    
    VERBOSE: Simple
    
    VERBOSE: Loading Module
    
    VERBOSE: Setting recovery
    
    The property 'RecoveryModel' cannot be found on this object. Verify that the property exists and can be set.
    
    At line:25 char:1
    
    + $Srv.Databases[$Database].RecoveryModel = $Recovery
    
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    
    + FullyQualifiedErrorId : PropertyNotFound
    
    VERBOSE: Error: You cannot call a method on a null-valued expression.
    
    

     

    I don't understand why this is happening.

    All advice and comments greatfully received.

  • #113419

    Participant
    Points: 20
    Rank: Member

    Are you in PowerShell version 2.0 ? [PSCustomObject] type behaviour is different in v 2.0.
    User Trace-Command to see what is happening in Parameter binding.

    Trace-Command ParameterBinding { [PSCustomObject]$DbInfo | Set-RecoveryModel } -PSPath
    
    • #113425

      Participant
      Points: 3
      Rank: Member

      Thanks for the help. I wasn't aware of that Trace-Command.

       

  • #113420
    js

    Participant
    Points: 6
    Rank: Member

    It should work fine, but the begin block isn't going to see any of the parameters from the pipe. You have to work with them in the process block. Notice that the verbose messages are blank when you pipe them.

    • #113422

      Participant
      Points: 3
      Rank: Member

      Thanks JS that indeed solved the problem.

  • #113428

    Participant
    Points: 3
    Rank: Member

    Here's what I ended up with, confirmed functioning as wanted.

    Invoke-SqlCmd -ServerInstance $SqlServer -Query "Select [SqlServer] = @@Servername, [Database] = name, [Recovery] = 'Simple' from master.sys.databases where database_id > 4 AND name like 'reporting_%' AND state_desc = 'ONLINE' AND user_access_desc = 'MULTI_USER' AND recovery_model_desc != 'SIMPLE'" | Set-RecoveryModel -Verbose
    
    
    Function Set-RecoveryModel
    
    {
    
    [CmdletBinding()]
    
    param (
    
    [Parameter(Mandatory=$true, ValuefromPipelineByPropertyName=$true,Position=0)]
    
    [Alias('IPAddress','CN')]
    
    [string]$SqlServer ,
    
    [Parameter(Mandatory=$true, ValuefromPipelineByPropertyName=$true,Position=1)]
    
    [string]$Database ,
    
    [Parameter(Mandatory=$false, ValuefromPipelineByPropertyName=$true,Position=2)]
    
    [string]$Recovery = "Simple"
    
    )
    
    BEGIN {
    
    Write-Verbose "Loading Module"
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
    
    }
    
    PROCESS {
    
    Write-Verbose $SqlServer
    
    Write-Verbose $Database
    
    Write-Verbose $Recovery
    
    Write-Verbose "Setting recovery"
    
    $Srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SqlServer
    
    $Srv.Databases[$Database].RecoveryModel = $Recovery
    
    Try {
    
    $Srv.Databases[$Database].Alter()
    
    } catch {
    
    Write-Verbose "Error: $($_.Exception.Message)`r`n$($_.Exception.ItemName)"
    
    Continue
    
    }
    
    $Srv.Databases[$Database].Refresh()
    
    }
    
    END {
    
    Write-Verbose "All Done"
    
    }
    
    }
    
    

You must be logged in to reply to this topic.