Using SQL Server Identifiers in PowerShell
When you use the SQL Server provider for Windows PowerShell with the Database Engine, you must provide names of both the computer and the instance, even for default instances.
The provider uses SQL Server identifiers in Windows PowerShell paths. SQL Server identifiers can contain characters that Windows PowerShell does not support in paths. You must escape these characters or use special encoding for them when using the identifiers in Windows PowerShell paths.
Computer Names
The first node following the SQLSERVER:\SQL is the name of the computer that is running the instance of the Database Engine; for example, SQLSERVER:\SQL\MyComputer. If you are running Windows PowerShell on the same computer as the instance of the Database Engine, you can use either localhost or (local) instead of the name of the computer. Scripts that use localhost or (local) can be run on any computer without having to be changed to reflect the different computer names. For example, this command will go to the AdventureWorks sample database in the default instance on the local computer:
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks
The parenthesis characters in (local) are normally treated as commands by Windows PowerShell. You must either:
Enclose path strings in quotes:
Set-Location "SQLSERVER:\SQL\(local)\DEFAULT\Databases\AdventureWorks" Set-Location "SQLSERVER:\SQL\(local)\DEFAULT\Databases\AdventureWorks"Escape the parenthesis using the back tick character (`).
Set-Location SQLSERVER:\SQL\`(local`)\DEFAULT\Databases\AdventureWorks Set-Location SQLSERVER:\SQL\`(local`)\DEFAULT\Databases\AdventureWorksEncode the parenthesis using their hexadecimal representation.
Set-Location SQLSERVER:\SQL\%28local%29\DEFAULT\Databases\AdventureWorks Set-Location SQLSERVER:\SQL\%28local%29\DEFAULT\Databases\AdventureWorks
Escaping and encoding characters are discussed in more detail later in this topic.
You cannot use a period (.) to specify the local computer in Windows PowerShell scripts. The period is not supported because the period is interpreted as a command by Windows PowerShell.
Default Instance Names
You can run multiple instances of the Database Engine executable program on the same computer. Instances of the Database Engine are identified by the combination of the name of the computer and an instance name, such as MyComputer\MyInstance.
Each computer can have one default instance of the Database Engine. You do not specify a name for the default instance when you install it. If you specify only a computer name in a connection string, you are connected to the default instance on that computer. All other instances on the computer must be named instances. You specify the instance name during setup, and connection strings must specify both the computer name and the instance name.
The SQL Server provider requires that you always specify an instance name. For default instances, you must specify an instance name of DEFAULT.
SQL Server Identifiers in Windows PowerShell Paths
Windows PowerShell providers expose data hierarchies using a path structure similar to that used for the Windows file system. The SQL Server provider implements paths to SQL Server objects. For the Database Engine, the drive is set to SQLSERVER:, the first folder is set to \SQL, and the database objects are referenced as containers and items. This is the path to the Vendor table in the Purchasing schema of the AdventureWorks database in a default instance of the Database Engine:
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks\Tables\Purchasing.Vendor
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks\Tables\Purchasing.Vendor
SQL Server identifiers are the names of SQL Server objects, such as table or column names. There are two types of SQL Server identifiers:
Regular identifiers are limited to a set of characters that are also supported in Windows PowerShell paths. These names can be used in Windows PowerShell paths without being changed.
Delimited identifiers can use characters not supported in Windows PowerShell path names. Delimited identifiers are called bracketed identifiers if they are enclosed in brackets ([IdentifierName]) and quoted identifiers if they are enclosed in double quotes ("IdentifierName"). If a delimited identifier uses characters not supported in Windows PowerShell paths, the characters must either be encoded or escaped before using the identifier as a container or item name. Encoding works for all characters. Some characters, such as the colon character (:), cannot be escaped.
Encoding and Decoding Identifiers
Characters that are not supported in Windows PowerShell path names can be represented, or encoded, as the "%" character followed by the hexadecimal value for the bit pattern that represents the character, as in "**%**xx". Encoding can always be used to handle characters that are not supported in Windows PowerShell paths.
The Encode-SqlName cmdlet takes as input a SQL Server identifier. It outputs a string with all the characters that are not supported by the Windows PowerShell language encoded with "%xx". The Decode-SqlName cmdlet takes as input an encoded SQL Server identifier and returns the original identifier. For example:
This command returns the string "Table%3ATest":
Encode-SqlName "Table:Test" Encode-SqlName "Table:Test"This command returns "Table:Test":
Decode-SqlName "Table%3ATest" Decode-SqlName "Table%3ATest"
When you specify delimited identifiers in Windows PowerShell cmdlets, you can either provide the encoded character values yourself, or use Encode-SqlName to provide the encoded characters. For example, if you have already navigated to the schema that contains the table [Table:Test], you can cd to the table by providing the encoded version of the ":" character:
Set-Location Table%3ATest
Set-Location Table%3ATest
Alternatively, you can use Encode-SqlName to build a name supported by PowerShell:
Set-Location (Encode-SqlName "Table:Test")
Set-Location (Encode-SqlName "Table:Test")
These are the characters encoded by Encode-SqlName and decoded by Decode-SqlName:
Character |
\ |
/ |
: |
% |
< |
> |
* |
? |
[ |
] |
| |
Hexadecimal Encoding |
%5C |
%2F |
%3A |
%25 |
%3C |
%3E |
%2A |
%3F |
%5B |
%5D |
%7C |
Escaping Characters
You can often use the Windows PowerShell back-tick escape character (`) to escape characters that are allowed in SQL Server delimited identifiers but not Windows PowerShell path names. Some characters, however, cannot be escaped. For example, you cannot escape the colon character (:) in Windows PowerShell. Identifiers with that character must be encoded. Encoding is more reliable than escaping because encoding works for all characters.
This is an example of escaping a # character:
cd SQLSERVER:\SQL\MyComputer\MyInstance\MyDatabase\MySchema\`#MyTempTable
cd SQLSERVER:\SQL\MyComputer\MyInstance\MyDatabase\MySchema\`#MyTempTable
The back-tick character (`) is usually on the key in the upper left of the keyboard, under the ESC key.
SQL Server Identifiers in cmdlets
Some SQL Server cmdlets have a parameter that takes an identifier as input. The parameter values are typically supplied as quoted string constants or in string variables. When identifiers are supplied as string constants or in variables, there is no conflict with the set of characters that are supported by Windows PowerShell.