Pipeline multiple parameters fails

PowerShell Help
1

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.

2

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
3

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.

4

Thanks JS that indeed solved the problem.

5

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

 

6

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"

}

}