Escaping a Dollar Sign in PoSh–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve been working more with PowerShell lately and ran into a problem I thought would be easy, but it wasn’t. So I decided to blog what I learned.

Assigning a String

I had a string that I wanted to use in a command. In this case, the Start-Service command. Here’s what I wrote:

Start-Service -Name “SQLAgent$$SQL2016_QA”

This didn’t work, mainly because of this error.

2016-11-15 14_32_20-powershell

PoSh thinks my string is “SQLAgent\”, not “SQLAgent$SQL2016_QA”. That’s strange, at least to me. Let’s try a variable.

2016-11-15 14_33_06-powershell

OK, I know I need to escape the dollar sign. I ran a quick Google search, because that’s way quicker than looking through documentation and found a piece on escaping strings. It mentions the backtick (`) as the character to use. Let’s try that.

2016-11-15 14_35_41-powershell

It works. Now to try to start the service.

2016-11-15 14_38_46-cmd - powershell (Admin)

No error, but it is it running? Yes.

2016-11-15 14_39_26-cmd - powershell (Admin)

This is a quick look at string work in PoSh. The more I try to automate work and get things to run themselves, the handier I find PoSh. Since I tend to work with named instances, this was valuable.

#SQLNewBlogger

This was one of those items that I spent about 5-10 minutes figuring out and then another 10 minutes shooting screens and duplicating my work. The writeup was easy, and it will help me remember how to do that in the future.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

11 Responses to Escaping a Dollar Sign in PoSh–#SQLNewBlogger

  1. Hey Steve! Nice post, loving all of the PowerShell 😀 You can also escape dollar signs by using the single quote, which creates a literal string.

    Get-Service -ComputerName sqlserver -Name ‘SQLAgent$VNEXT’

    • way0utwest says:

      Ah, thanks, Chrissy. I think I saw that somewhere, but I get confused about when single or double quotes work at times. Is there a good reference or guideline of where/when to use each?

    • Honestly, and this slightly controversial position, I use double quotes by default. The reason is, there’s usually a variable inside of my string anyway. So for instance:

      $servername = “sql2016” # could be ‘sql2016’, both work
      Write-Output “Could not connect to $servername”

      Th second line would output “Could not connect to sql2016”.

      If I were to execute the following: Write-Output ‘Could not connect to $servername’

      That line would output “Could not connect to $servername” because the single quotes made it a literal and therefore the $servername was not evaluated.

      The exceptions that I make are when literals are needed, like in your script today. Using a single quote is a lot prettier than backticks. Sometimes, however, backticks are necessary but for me, it’s rare.

      Warren Frame did some testing and found that single quotes are like a millionth of a second faster than double quotes. Not enough for me to justify moving to the single quote by default. I tried for a day and quickly became frustrated because so many of my outputs do contain variables anyway.

      By the way, PowerShell handles strings in such a cool way. This is one of my favorite things to do.

      $sql = “Select whatever
      from table
      where server =’$servername'”

      Note that the string spans multiple lines and I didn’t have to do anything special. I also really like TrimEnd() and TrimStart(). $sql.TrimStart(“Select”) would take off the Select! How cool 🙂

    • I used to use doubles by default, but I’ve had enough issues with unexpected chars in data, that I’ve mostly switched to single quotes unless there’s a reason not to. Even in SQL DB and table names I constantly get back a bunch of crap in the strings so I find it best to use literals unless I specifically need expansion. But to each his own.

    • way0utwest says:

      Thanks for the thoughts. I’ll try to play with both and see what I think.

    • And one more thing, see how your comment formatting makes the single and double quotes fancy? PowerShell handles that, too! It interprets the fancy characters as though they were the regular characters.

    • way0utwest says:

      Love the inconsistency. Not.

      OK, I’ll likely stick with your method. Mostly doubles, then singles if I need them.

  2. This is also useful to know if you use Ola Hallengren’s backup scripts with Availability Groups. The script will automatically put backups in a path that includes the cluster name and AG name separated by a dollar sign (for example, X:\backups\mycluster$myAG\). If you do any PoSh automation with those backups, character escaping is a must.

    • way0utwest says:

      That’s a good thing to know. Thanks.

    • And if you use the Minion scripts, you don’t have that issue at all. In fact, in Minion Backup 1.3 that’s coming out soon, we’re giving you dynamic strings that you can use in both the path and the filenames. And even now we don’t constrain you to using the AG name in your path if you don’t want to.

Comments are closed.