Handling Periods In Database Names With PowerShell

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:

CREATE DATABASE [Terrible.DBName]

 

Now open up PowerShell and try to query what’s in that database:

dir SQLSERVER:SQLlocalhostdefaultdatabasesTerrible.DBName


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
   Command

I tried encapsulating the path in quotes and escaping the period with a back tick, both to no avail.

dir ‘SQLSERVER:SQLlocalhostdefaultdatabasesTerrible.DBName’
dir SQLSERVER:SQLlocalhostdefaultdatabasesTerrible`.DBName


 

Fixing the problem

Eventually I found the solution. Changing the period to it’s hex value of %2e allowed me to query correctly.

dir SQLSERVER:SQLlocalhostdefaultdatabasesTerrible%2eDBName

 

PeriodInDBName

 

 

Updating scripts

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:

$a = “Some.String”
$a = $a -replace ".", "%2e"

$a


PeriodInDBNameWrongString

 

The right way:

$a = "Some.String"

$a = $a -replace "[.]", "%2e"

$a


PeriodInDBNameRightString

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s