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.