Getting a dynamic database parameter on a SQL server

Question

I'm creating a PS function with 2 parameters: $server & $database. I need the $database parameter to be auto-populated (dynamic validation set), depending on the first parameter ($server)

I got most of the code from here

However it is NOT working. What am I doing wrong here? Any insight is greatly appreciated. Thank you.

function Get-databases {
    [CmdletBinding()]
    Param(
        # Any other parameters can go here              

        [Parameter(Mandatory)][string] $Server 

    )

    DynamicParam {
            # Set the dynamic parameters' name
            $ParameterName = 'Database'

            # Create the dictionary 
            $RuntimeParameterDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary

            # Create the collection of attributes
            $AttributeCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]

            # Create and set the parameters' attributes
            $ParameterAttribute = New-Object System.Management.Automation.ParameterAttribute
            $ParameterAttribute.Mandatory = $true
            $ParameterAttribute.Position = 1

            # Add the attributes to the attributes collection
            $AttributeCollection.Add($ParameterAttribute)

            # Generate and set the ValidateSet             
            $arrSet = (Invoke-Sqlcmd -ServerInstance $server  -query 'select name from sys.databases order by 1'   -ConnectionTimeout 60 -QueryTimeout 99999).name                         
            $ValidateSetAttribute = New-Object System.Management.Automation.ValidateSetAttribute($arrSet)

            # Add the ValidateSet to the attributes collection
            $AttributeCollection.Add($ValidateSetAttribute)

            # Create and return the dynamic parameter
            $RuntimeParameter = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName, [string], $AttributeCollection)
            $RuntimeParameterDictionary.Add($ParameterName, $RuntimeParameter)
            return $RuntimeParameterDictionary
    }

    begin {
        # Bind the parameter to a friendly variable
        $db = $PsBoundParameters[$ParameterName]
    }

    process {
        # Your code goes here
        $db 

    }

}

Post the error if u are getting any, an the above code doesn't set a value for the Dynamicparameter, it just adds values to the ValidateSet attribute which will be used only when there is a value given to that parameter. — Prasoon Karunan V, Oct 28 '17 at 14:23
Thank you Prasoon. I understand your comment. I want to pass the first variable ($server) to the second variable ($database). So that I can get the databases belonging only to the server in question. I may be doing it wrong. Probably this is not the way to achieve it. If you have a better option, that would work too. — RaviLobo, Oct 28 '17 at 15:24
Invoke-SqlCmd can take time to get the data from DB, try giving some sample array value instead for testing — Prasoon Karunan V, Oct 28 '17 at 18:11
Interesting! I'm going to try that. I'll let you know. Thank you. — RaviLobo, Oct 29 '17 at 20:47
The DBAtools ps module has many functions with parameter $database which is auto-populated depending of $sqlserver. I was trying to achieve that, but no luck. Here's the pseudo-code: `Function Get-something { [CmdletBinding(DefaultParameterSetName = "Default")] Param ( [parameter(Mandatory = $true, ValueFromPipeline = $true)] [object[]] $SqlServer ) dynamicparam { if ($SqlServer) { return Get-ParamSqlDatabases -SqlServer $SqlServer[0] -SqlCredential $Credential } } begin { } }` — RaviLobo, Oct 30 '17 at 12:43
sorry about the formatting in the above post. somehow the back tics for code is not working for me! — RaviLobo, Oct 30 '17 at 12:47

score 1 · Accepted Answer · answered Oct 31 '17 at 17:11

1

If you have Invoke-SqlCmd in DynamicParam ValidateSet attribute, your tab completion is going to execute the whole Invoke-SqlCmd for validation , this is very expensive w.r.t performance.

You can give some xyz value to -DataBase without using Tab completion, you will see it validating the Input, but will take little time as it will execute the Invoke-SqlCmd for the validation .

So I would advice not to go with DynamicParams or to Avoid Validation within DynamicParam, you can have an explicit validation in Begin block.

answered Oct 31 '17 at 17:11

Prasoon Karunan V

2,916
2
12
26

Prasoon, PowerShell Gallery has a module for DBAs called DBATOOLS; this module has many functions, that have the feature I asked in my question. I tweeted my question to DBATool folks, but didn't get any response. I know it is achievable; I just don't know how. – RaviLobo Oct 31 '17 at 20:49
DBATOOLS does it like this, https://github.com/sqlcollaborative/dbatools/blob/ac49b4c26b33c746f8e6c69c6a89052af2655709/functions/Restore-DbaBackupFromDirectory.ps1 And is not expensive... – Prasoon Karunan V Nov 01 '17 at 07:22
Great! I had to tweak the code a little bit. Restore-DbaBackupFromDirectory doesn't actually talks to SQL Server. However, it set me on the right path. You deserve the bounty. I'll also, post my code, for anyone who is looking for similar thing. Thank you again. – RaviLobo Nov 01 '17 at 13:02

RaviLobo · Answer 2 · 2018-03-07T13:44:29.917

Here's the final code:

import-module sqlps
function Get-Database {
    <# 
        .SYNOPSIS 
            Dynamic validationset of databases      

    #>  
    #Requires -Version 3.0
    [CmdletBinding()]
    Param (
        [Parameter(Mandatory)][string]$server

    )

    DynamicParam {

        $newparams = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
        $paramattributes = New-Object System.Management.Automation.ParameterAttribute
        $paramattributes.ParameterSetName = "__AllParameterSets"
        $paramattributes.Mandatory = $false
        $systemdbs = @("master", "msdb", "model", "SSIS", "distribution")

        $srv = New-Object 'Microsoft.SqlServer.Management.SMO.Server' "$server"
        $dblist = ($srv.Databases).name | Where-Object { $systemdbs -notcontains $_ }

        $argumentlist = @()

        foreach ($db in $dblist) {
            $argumentlist += [Regex]::Escape($db)
        }

        $validationset = New-Object System.Management.Automation.ValidateSetAttribute -ArgumentList $argumentlist
        $combinedattributes = New-Object -Type System.Collections.ObjectModel.Collection[System.Attribute]
        $combinedattributes.Add($paramattributes)
        $combinedattributes.Add($validationset)
        $Databases = New-Object -Type System.Management.Automation.RuntimeDefinedParameter("Databases", [String[]], $combinedattributes)        
        $newparams.Add("Databases", $Databases)     
        return $newparams

    }

    process {

        $UserDb = $psboundparameters.Databases

        Write-Host "You picked: $UserDb"
    }
}

Clear-Host 
Get-Database -server 'YourServerName' -Databases 'DynamicallyPopulatedDatabases'

Getting a dynamic database parameter on a SQL server

2 Answers2

Linked