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:\SQL\localhost\default\databases\Terrible.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:\SQL\localhost\default\databases\Terrible.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:\SQL\localhost\default\databases\Terrible.DBName'    

dir SQLSERVER:\SQL\localhost\default\databases\Terrible`.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:\SQL\localhost\default\databases\Terrible%2eDBName  

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

The right way:

$a = "Some.String"

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

$a

Update on Tuesday, May 10, 2011 at 7:16PM by Nic

Sean McCown (blog|twitter) pointed out in the comments the use of Encode-SQLName to take care of this. It makes a lot more sense and will handle all manner of random characters that may appear in the database name.

Here's an example:

$db = "Terrible.DBName"

$db = Encode-SQLName -SQLName $db

dir SQLSERVER:\SQL\localhost\default\databases\$db

$db

Thanks for pointing that out Sean.

Comments

[Sean McCown]

This is actually what Encode-SQLName is for. It works like this. I'll use your example:

$a = "terrible.DBName"
$a = Encode-SQLName -SQLName $a

$a

That'll give you the same results while not having to do a separate replace for every permutation of special chars you come across.

[Nic]

Where were you when I was banging my head against the wall trying to figure this out?

Thanks Sean, that's a much more elegant solution than mine and would cover the event of other special characters being included in the database name which mine wouldn't.