PowerShell Function – Validating a Parameter Depending On A Previous Parameter’s Value

I was chatting on the SQL Community Slack with my friend Sander Stad b | t about some functions he is writing for the amazing PowerShell SQL Server Community module dbatools. He was asking my opinion as to how to enable user choice or options for Agent Schedules and I said that he should validate the input of the parameters. He said that was difficult as if the parameter was Weekly the frequency values required would be different from if the parameter was Daily or Monthly. That’s ok, I said, you can still validate the parameter.

You can read more about Parameters either online here or here or by running

Get-Help About_Parameters
Get-Help About_Functions_Parameters

You can also find more help information with

Get-Help About_*Parameters*

01 more help.PNG

This is not a post about using Parameters, google for those but this is what I showed him.

Lets create a simple function that accepts 2 parameters Word and Number

 function Test-validation
{
    Param
    (
         [string]$Word,
         [int]$Number
    )
Return "$Word and $Number"
} 

We can run it with any parameters

02 any parameters

If we wanted to restrict the Word parameter to only accept Sun, Moon or Earth we can use the ValidateSetAttribute as follows

 function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [int]$Number
    )
Return "$Word and $Number"
}

Now if we try and set a value for the $Word parameter that isn’t sun moon or earth then we get an error

03 parameter error.PNG

and it tells us that the reason for the error is that TheBeard! does not belong to the set sun, moon, earth.

But what Sander wanted was to validate the value of the second parameter depending on the value of the first one. So lets say we wanted

  • If word is sun, number must be 1 or 2
  • If word is moon, number must be 3 or 4
  • If word is earth, number must be 5 or 6

We can use the ValidateScriptAttribute  to do this. This requires a script block which returns True or False. You can access the current parameter with $_ so we can use a script block like this

{
    if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
    elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
    elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
}

The function now looks like

function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [ValidateScript({
            if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
            elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
            elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
        })]
        [int]$Number
    )
Return "$Word and $Number"
}

It will still fail if we use the wrong “Word” in the same way but now if we enter earth and 7 we get this

04 parameter error.PNG

But if we enter sun and 1 or moon and 3 or earth and 5 all is well

05 working

I would add one more thing. We should always write PowerShell functions that are easy for our users to self-help. Of course, this means write good help for the function. here is a great place to start from June Blender

06 June.PNG

In this example, the error message

Test-validation : Cannot validate argument on parameter ‘number’. The ”
if($word -eq ‘Sun’){$_ -eq 1 -or $_ -eq 2}
elseif($word -eq ‘Moon’){$_ -eq 3 -or $_ -eq 4}
elseif($word -eq ‘earth’){$_ -eq 5 -or $_ -eq 6}
” validation script for the argument with value “7” did not return a result of True. Determine why the validation script failed, and then try the
command again.
At line:1 char:39
+ Test-validation -Word “earth” -number 007
+                                       ~~~
+ CategoryInfo          : InvalidData: (:) [Test-validation], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationError,Test-validation

is not obvious to a none-coder so we could make it easier. As we are passing in a script block we can just add a comment like this. I added a spare line above and below to make it stand out a little more

function Test-validation
{
    Param
    (
        [ValidateSet("sun", "moon", "earth")]
        [string]$Word,
        [ValidateScript({
            #
            # Sun Accepts 1 or 2
            # Moon Accepts 3 or 4
            # Earth Accepts 5 or 6
            #
            if($Word -eq 'Sun'){$_ -eq 1 -or $_ -eq 2}
            elseif($Word -eq 'Moon'){$_ -eq 3 -or $_ -eq 4}
            elseif($Word -eq 'earth'){$_ -eq 5 -or $_ -eq 6}
        })]
        [int]$Number
    )
Return "$Word and $Number"
}

Now if you enter the wrong parameter you get this

07 more help.PNG

which I think makes it a little more obvious

 

Advertisements

PowerShell Pester Testing for Parameter Validation

This error caught me out. I am putting this post here firstly to remind me if I do it again adn also to help others who may hit the same issue.

Today I am rewriting a function to create a Hyper-V VM so that I can properly script the creation of my labs for demos and other things. I am doing this because I want to use DSC to create an availability group and want to be able to tear down and recreate the machines (but thats for another day)

I also have been looking at Pester which is a framework for running unit tests within PowerShell

You will find some good blog posts about starting with Pester here

Here is the start of the function. I validate the VMName parameter to ensure that there a VM with that  name does not already exist

function Create-HyperVMFromBase { 
[cmdletbinding()] 
param (
 [Parameter(Mandatory = $true,HelpMessage="Enter a VMName for the VM that does not exist")] [ValidateScript({(!(Get-VM -Name $_))})] 
[string]$VMName,

and my Pester test looks like this

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path).Replace(".Tests.", ".")
. {'$here\$sut'}

Describe "Create Hyper V from Base Tests" {
    Context "Parameter Values,Validations and Errors" {
        It exists {
        test-path function:\create-hypervmfrombase | should be $true
        }
        It "Should error when VMName exists" {
        $VMName = (Get-VM|Select -First 1 Name).Name
        create-hypervmfrombase -VMName $VMName |should throw
        }

I thought that what I was testing was that the function threw an error when an incorrect parameter was passed. The should throw should be true but what I got was

pester error3

So I was getting the correct error but not passing the test. It was a simple fix. Simply adding curly braces around the call to the function

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path).Replace(".Tests.", ".")
. "$here\$sut"
Describe "Create Hyper V from Base Tests" {
    Context "Parameter Values,Validations and Errors" {
        It exists {
        test-path function:\create-hypervmfrombase | should be $true
        }
        It "Should error when VMName exists" {
        $VMName = (Get-VM|Select -First 1 Name).Name
        {create-hypervmfrombase -VMName $VMName} |should throw
        }
    }
}

and we pass the test.

pester success2

PowerShell Pester – "The script failed due to call depth overflow."

This error caught me out. I am putting this post here firstly to remind me if I do it again and also to help others who may hit the same issue.

I also have been looking at Pester which is a framework for running unit tests within PowerShell

You will find some good blog posts about starting with Pester here

So I created a function script file Create-HyperVFromBase.ps1 and a tests script file Create-HyperVFromBase.tests.ps1 as shown.

pester scripts

The tests contained this code

 $here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path).Replace(".Tests.", ".")
. {'$here\$sut'}
Describe "Create Hyper V from Base Tests" {
    Context "Parameter Values,Validations and Errors" {
        It exists {
        test-path function:\create-hypervmfrombase | should be $true
        } 
}
}

When I ran the test I got the following error

pester error1

or

pester error2

Googling pester “The script failed due to call depth overflow.” returned only 7 results but the Reddit link contained the information I needed

.Replace() is case sensitive. It didn’t remove the .tests. keyword from your file name. So it calls your test script again and repeats the same mistake over and over.

and so I renamed the tests script file to Create-HyperVFromBase.Tests.ps1 With a Capital T! and bingo

pester success

Don’t forget to name your Pester Tests scripts with a capital T when loading the script in this way and remember that Replace() is case sensitive.

$here = Split-Path -Parent $MyInvocation.MyCommand.Path
$sut = (Split-Path -Leaf $MyInvocation.MyCommand.Path).Replace(".Tests.", ".")
. "$here\$sut"

Creating SQL Server Database with PowerShell

 

This morning I have been setting up my Azure Servers in preparation for my presentation to the Cardiff SQL User Group this month.

I used my scripts from My Post on Spinning Up Azure SQL Boxes to create two servers and then I wanted to create some databases

I decided it was time to write a Create-Database function using a number of scripts that I have used to create individual databases.

Errors

Whilst finalising the function I didn’t quite get it right sometimes and was faced with an error.

image

Not the most useful of errors to troubleshoot. The issue could be anywhere in the script

You can view the last errors PowerShell has shown using $Errors. This gives you the last 500 errors but you can see the last error by using $Error[0] if you pipe it to Format-List you can get a more detailed error message so I added a try catch to the function which gave me an error message I could resolve.

image

Much better. The problem was

Cannot create file ‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\.LDF’ because it already exists.

Mistyping a variable has caused this. Creating an empty file name variable which then threw the error the second(and third,fourth fifth) times I ran the script but this error pointed me to it.

Creating Database

There are a vast number of variables you can set when creating a database. I decided to set File Sizes, File Growth Sizes, Max File Sizes and Recovery Model. I only set Server and Database Name as mandatory parameters and gave the other parameters default values

image

We take the parameters for file sizes in MB and set them to KB

image

Then set the default file locations. Create a database object, a Primary file group object and add the file group object to the database object

image

Add a User File Group for User objects

image

Create a database file on the primary file group using the variables set earlier

image

Do the same for the user file and then create a Log File

image

Set the Recovery Model and create the database and then set the user file group as the default

image

Finally catch the errors

image

It can then be called as follows Create-Database SERVERNAME DATABASENAME

image

or by setting all the parameters Create-Database -Server Fade2black -DBName DatabaseTest -SysFileSize 10 -UserFileSize 15 -LogFileSize 20 -UserFileGrowth 7 -UserFileMaxSize 150 -LogFileGrowth 8 -LogFileMaxSize 250 -DBRecModel FULL

image

This means that I can easily and quickly set up several databases of different types and sizes

The script can be found here Create-Database