Parameter sets not adhered to

I’ve got a Function that has 2 parameter sets, the purpose is to carry out a task on all databases on a server, or just the named database. When I pass just a server name, I get an error “You cannot call a method on a null-valued expression”, works fine if I pass server & database name. I checked the parameters and I see the database parameter is showing to “all” for parameter sets. Why ?

-Database <string[]>

Required? false
Position? Named
Accept pipeline input? true (ByValue, ByPropertyName)
Parameter set name (All)
Aliases None
Dynamic? false

-SqlServer <string[]>

Required? false
Position? Named
Accept pipeline input? true (ByValue, ByPropertyName)
Parameter set name Single, Multiple
Aliases None
Dynamic? false

[CmdletBinding()]
Param(
[parameter(ParameterSetName='Multiple')]
[parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True,ParameterSetName='Single')]
[string[]]$SqlServer,
[parameter(ParameterSetName='Single')]
[parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True)]
[string[]]$Database
)

I’m calling the function like this:

@([pscustomobject]@{
SqlServer = "sqlserver\instance"
Database = "database1"
}),

@([pscustomobject]@{
SqlServer = "sqlserver\instance"
Database = "database2"
}) | Recover-LogSpace

 

…and like this:

@([pscustomobject]@{
SqlServer = "sqlserver1\instance"
}),

@([pscustomobject]@{
SqlServer = "sqlserver2\instance"
}) | Recover-LogSpace

Try something like this:
[pre]
[CmdletBinding(DefaultParameterSetName = ‘Multiple’)]
Param(
[parameter(ParameterSetName=‘Multiple’)]
[parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True)]
[string[]]$SqlServer,

    [parameter(ParameterSetName='Single')]
    [parameter(ParameterSetName='Multiple')]
    [parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True)]
    [string[]]$Database
)

[/pre]

So I took what you provided and plugged it into a dummy function that just spits the input back out. No issues. Your error is likely coming from somewhere else in your function, so it would be useful to see the rest of it. The full text of the error should also tell you whereabouts it’s failing.

Thanks Mark, here it is in full…

Function Recover-LogSpace
{
[CmdletBinding()]
Param(
[parameter(ParameterSetName='Multiple')]
[parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True,ParameterSetName='Single')]
[string[]]$SqlServer,
[parameter(ParameterSetName='Single')]
[parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True)]
[string[]]$Database
)

BEGIN {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
}
PROCESS {
foreach($server in $SqlServer) {
"Connecting to $server"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
if($Database) {
"Working on database $Database"
$srv.Databases[$Database] | ?{ $_.Shrink(2,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]'TruncateOnly') }
}
else {
"Working on all databases on server"
$srv.Databases | ?{ $_.ID -gt "4" -And $_.IsAccessible -eq $True -And $_.ReadOnly -eq $False } | ?{ $_.Shrink(2,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]'TruncateOnly') }
}
}
}
END {
}
}

Wes, I had previously put Multiple as default, and juggling the parameter configs around, but still same problem. I did still try your suggestion, but same problem.

Thanks for suggestion though.

What’s the full text of your error?

I’ve lost track a bit of what I’ve changed, or doing differently, but I’m now getting a different result…

When I run this:

@([pscustomobject]@{
SqlServer = "sqlserver1\instance"
}),

@([pscustomobject]@{
SqlServer = "sqlserver2\instance"
}) | Recover-LogSpace

 

I now get this:

Connecting to @{SqlServer=sqlserver2\instance}
Working on database @{SqlServer=sqlserver2\instance}
Connecting to @{SqlServer=sqlserver1\instance}
Working on database @{SqlServer=Tsqlserver1\instance}

 

Function is:

Function Recover-LogSpace
{
[CmdletBinding()]
Param(
[parameter(ParameterSetName='Multiple')]
[parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True,ParameterSetName='Single')]
[string[]]$SqlServer,
[parameter(ParameterSetName='Single')]
[parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True)]
[string[]]$Database
)

BEGIN {
# https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.shrinkmethod?view=sqlserver-2016
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
}
PROCESS {
foreach($server in $SqlServer) {
"Connecting to $server"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
if($Database) {
"Working on database $Database"
$srv.Databases[$Database] | ?{ $_.Shrink(2,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]'TruncateOnly') }
}
else {
"Working on all databases on server"
$srv.Databases | ?{ $_.ID -gt "4" -And $_.IsAccessible -eq $True -And $_.ReadOnly -eq $False } | ?{ $_.Shrink(2,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]'TruncateOnly') }
}
}
}
END {
}
}

After looking at it a bit, I think you’re overcomplicating it with parameter sets. You can cut those out, make the $SqlServer parameter mandatory since it always needs to be used, and then check for the $Database parameter without changing up your logic.

Take a look at this sample that I’ve been playing with…

https://gist.github.com/markroloff/87d69dde2ff03fd358bb97bafd8bbb92

 

I could not use parameter sets, yep, but i’d rather figure out why this is happening and use them.

The core issue you have is this:

[parameter(ParameterSetName='Single')]
[parameter(ValueFromPipelineByPropertyName=$true,ValuefromPipeline=$True)]

Each [Parameter()] attribute can be flagged as belonging to one set only, though a parameter may belong to multiple sets. As you have it, the parameter tagged with these two attributes is now belonging to both the default (‘All’) set and the Single set.

In order to do this as you want it to be done, you need to move the ParameterSetName declaration into the other [Parameter()] attribute. Each [Parameter()] attribute should represent a single set membership and the mandatory or not properties that the parameter has in that particular set. Yes, you can have a parameter that is mandatory in one set while being optional in another. Careful, it gets weird!

Hopefully that makes sense!

Got it working from your last example. Ditch ValueFromPipeline and combine the parameter attributes on $Database.

ValueFromPipeline is used when a parameter needs to consume an entire object, rather than just a single property of that object. This is causing the funky output that you’re getting. The split attributes on $Database were also causing weird binding issues that went away when they were combined.

https://gist.github.com/markroloff/6279bce7213a13e56046776af4eb1378

Mark, Confirmed working as you demonstrated

I really appreciate your help with that…thank you.

Joel, thank you for your response. That helped me understand better what the issue was, appreciated.

Happy to and glad to hear! I never really mess with parameter sets, so it was good to learn something new today.