0

Sorry for trivial question but ...

I have an issue while trying to execute SQL command in PowerShell using invoke command

The issue starts when I tried to add a string into an SQL command:

$sqlNAVresutl = Invoke-SQLCMD -Query 'SELECT *
FROM [dbo].[DBO name]
WHERE [dbo].[Job Queue Entry].Status = 2 AND [dbo].[$Job Queue Entry].[Object ID to Run] = 52063060 AND [dbo].[$Job Queue Entry].[User ID] = 'Domain\Account'
GO
' 
 -ServerInstance "Server name" -Database "dbo name"

The issues starts when I add Domain\Account to the code. It works in SQL but not in PowerShell because the whole query is in single quotes (' ').

What can I do to read the string inside single quotes?

Konrad Rudolph
  • 530,221
  • 131
  • 937
  • 1,214
Wiktor
  • 581
  • 1
  • 12
  • 23
  • 1
    Use double single quotes: ` = ''Domain\Account''`. – Gordon Linoff Dec 10 '19 at 11:30
  • I got error Invoke-SQLCMD : Invalid column name 'Domain\Account'. – Wiktor Dec 10 '19 at 11:38
  • 2
    For multi-line SQL you're generally better off using here-strings (`@" .... "@`) so you don't have to worry about escaping (except for `$` if it could start a variable name, as that will still be interpolated -- if you really need a literal `$` in a query you'd use `\`$` to escape that). – Jeroen Mostert Dec 10 '19 at 11:39
  • @AjeetVerma Please reserve code formatting (using backticks) for actual *code* and don’t use it for emphasis or around acronyms. Many of your suggested edits are mis-applying this kind of formatting. Ironically for the one use where backticks would have been appropriate, you went for `**` emphasis instead. Have a look at my edit to this question to see what I mean. – Konrad Rudolph Dec 10 '19 at 15:33

2 Answers2

0

At -Query you could use double quotes as at -ServerInstance or -Database

Dávid Laczkó
  • 1,091
  • 2
  • 6
  • 25
  • I got error as well .. Invoke-SQLCMD : Invalid object name 'dbo.Queue Entry'. At line:1 char:17 + $sqlNAVresutl = Invoke-SQLCMD -Query "SELECT * + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand – Wiktor Dec 10 '19 at 11:39
  • If your dollar signs are not preceding variable names, escape them with a backtick (`$). – Dávid Laczkó Dec 10 '19 at 11:50
  • Ok somehow i figured it out ..... For now it works but strange :) – Wiktor Dec 10 '19 at 12:08
0

To embed ' (single quotes) in a string literal in PowerShell, you have 3 options:

Inside single-quoted strings ('...'), double them:

# ' doubled
'Honey, I''m home.'

Single-quoted strings are otherwise interpreted verbatim, so no other chars. need escaping.

Conversely, this means that no variable references or expressions are expanded (interpolated) in such strings; if that is needed - e.g., if $Job in your query string is meant to refer to the value of a $Job variable defined in PowerShell - you must use a double-quoted string ("...") - see below.

Inside single-quoted here-strings (@'<newline>...<newline>'@), you don't need to escape ' at all.

This always multi-line variant of a single-quoted string requires no escaping of any characters.

# No escaping needed (ignore the broken syntax highlighting)
@'
Honey, I'm home.
'@

Note: The closing '@ must be at the very start of the line - not even preceding whitespace allowed.

Inside double-quoted strings ("..."), you don't need to escape ' at all.

However, since such expandable strings (interpolating strings) by design interpret $ as the start of a variable reference (e.g., $var) or subexpression (e.g. $(1+2)), you'll then have to escape literal $ chars. as `$

  • ` (the backtick) generally serves as the escape character in PowerShell (see link below), so you would simililarly use `" to escape embedded " chars.

  • The need to escape literal $ chars. as `$ equally applies to the here-string variant of expandable strings
    (@"<newline>...<newline>"@), but embedded " do not need escaping.

# No escaping of ', but literal $ must be escaped as `$
"Honey, I'm home; I hate my `$Job."

See also:

  • about_Quoting_Rules, the official help topic on string literals.
    • about_Special_Characters, which explains the `-prefixed escape sequences supported inside double-quoted strings, such as `t to represent a tab character. Note that Windows PowerShell supports fewer sequences than PowerShell [Core] 6+.
  • This answer, which explains expandable strings (string interpolation) in detail.
mklement0
  • 382,024
  • 64
  • 607
  • 775