I came across an interesting problem today. We have a vendor who’s tool, for some unknown reason, creates a database with a period in the name (ie Data.Base). Why on earth anyone would do this I don’t know, but it’s happened. I found this out when my PowerShell script to capture database sizes failed.
It seems that PowerShell was not able to handle that period.
Reproducing the problem
Create a new database using SSMS:
Now open up PowerShell and try to query what’s in that database:
Doing so gives the error:
Get-ChildItem : Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: The number of keys specified does not match the number of keys required to address this object. The number of keys required are: Name.
At line:1 char:4
+ dir <<<< SQLSERVER:SQLlocalhostdefaultdatabasesTerrible.DBName
+ CategoryInfo : InvalidArgument: (:) [Get-ChildItem], ParameterBindingException
+ FullyQualifiedErrorId : GetDynamicParametersException,Microsoft.PowerShell.Commands.GetChildItem
I tried encapsulating the path in quotes and escaping the period with a back tick, both to no avail.
Fixing the problem
Eventually I found the solution. Changing the period to it’s hex value of %2e allowed me to query correctly.
Now that I found the problem and resolution I’ll be going through and changing my scripts to take this into account.
One thing to note, when trying to use –replace to replace the period in a variable you need to place it within square brackets otherwise the entire string gets replaced with %2e.
The wrong way:
The right way: