Art of the DBA Rotating Header Image

November 2nd, 2015:

A Month of SQLPS: Encoding and Decoding

Welcome back and thanks for waiting. There are just a couple cmdlets left to cover in this series, so I expect we’ll wrap up later this week. The next two are odd ones for a couple of reasons. The functions to cover are for SqlNames:

Get-Command -Module SQLPS *SqlName

SQLPS-21-1

What do these cmdlets even do? The help files describe the Encode-SqlName as a function for converting special characters in SQL Server names to characters that can be read by the Powershell provider. The reason this is necessary is because SQL Server objects will support characters in their names that are reserved within Powershell, specifically: : \:./%<>*?[]|

Using the cmdlets is simple enough. For the Encode-SqlName cmdlet, all we need to do is pass the name with those characters and the output will be something we can use within a provider path. The Decode-SqlName will reverse this process, taking the provider path value and convert it back to its SQL Server object name:

SQLPS-21-2

Do we even need these cmdlets? I really don’t think so. Firstly, I would strongly advise against using any of these reserved characters in a SQL Server object name. There are too many issues and problems that can arise, beyond this one, when these are used. Secondly, we already have ways of handling special characters in path names:

  • Wrapping the the full name in single or double quotes
  • Escaping the characters with the Powershell escape character: ` (the backtick/backquote)

SQLPS-21-3

The additional hiccup is that these are the two cmdlets that throw the warning when the SQLPS module is loaded. Encode and Decode are not approved Powershell verbs, which means the you will be warned that those cmdlets will be harder to discover because they don’t fit the language standard. While this is the case, the module still gets loaded and will operate just fine.

I’ve mentioned cmdlets that I like and use. I can’t say that I have ever used these cmdlets and don’t know what I would recommend them for. Honestly, I’d like to see the Microsoft team deprecate these and simply recommend the current methods for handling special characters in names.