How do quotes/strings work in Powershell?

Question

I'm have a command line that works in the regular old Windows Command Shell, but somehow gets misinterpreted in Powershell (I'm fairly new to Powershell).

sqlcmd -S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v DB="ksuite" -v OPTYPE="DB" -v BACKUPFOLDER="f:\SQLBackups" -v REPORTFOLDER="f:\SQLBackups\Reports" -v DBRETAINUNIT="days" -v DBRETAINVAL="7"

Powershell seems to be stripping the drive letters out of the arguments that require paths. For example, I get the following when I attempt to run the above command in Powershell:

Sqlcmd: ':\SQLBackups': Invalid argument. Enter '-?' for help.

Well sure it's invalid without the drive letter. I have tried variations on double quoting it, escaping it, etc. but can't get it to work. What am I missing that Powershell does differently?

Answer 1

Jaykul's start-process is how I do it as well. SQLCMD was designed with cmd.exe in mind and as a result there's some conflict between it wanting to see the quote characters and the way that Powershell uses them (or doesn't use them more to the point).

Here's what you can use for a straight-up call, should work:

sqlcmd -S .\SQLEXPRESS -i "`""f:\SQLBackups\ExpressMaint.sql`"" -v 'DB="ksuite"' -v 'OPTYPE="DB"' -v 'BACKUPFOLDER="`"'f:\SQLBackups'`"" -v 'REPORTFOLDER="`"'f:\SQLBackups\Reports'`"" -v 'DBRETAINUNIT="days"' -v 'DBRETAINVAL="7"'

Answer 2

For starters, PowerShell assumes that all parameters are either just a value, or a -name value pair. When you have quotes in the middle of the value, weird things happen...

You most likely just need to quote the whole parameter in those cases:

sqlcmd -S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v 'DB="ksuite"' -v 'OPTYPE="DB"' -v 'BACKUPFOLDER="f:\SQLBackups"' -v 'REPORTFOLDER="f:\SQLBackups\Reports"' -v 'DBRETAINUNIT="days"' -v 'DBRETAINVAL="7"'

However, my usual suggestion is to just use Start-Process, which takes all the arguments in a single string, so you can do:

Start-Process sqlcmd @"
-S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v DB="ksuite" -v OPTYPE="DB" -v BACKUPFOLDER="f:\SQLBackups" -v REPORTFOLDER="f:\SQLBackups\Reports" -v DBRETAINUNIT="days" -v DBRETAINVAL="7"
"@ -RedirectStandardOutput f:\SQLBackups\CapturedOutput.log

The -RedirectStandardOutput f:\SQLBackups\CapturedOutput.log on the last line there will just capture stdout to a file in case you needed it... if you need to log the error output, or need to provide input, you can redirect those to/from files also.

Answer 3

I don't have sqlcmd so I can't comment on how that is interpreting the parameters it is being passed but if I use the structure you are using to delivers the parameters to a batch file that just echoes the command that invoked the cmd shell (echo %cmdcmdline%) shows that what is being passed as the parameters is as follows:

cmd /c ""C:\Users\Joe\sqlcmd.cmd"  -S .\SQLEXPRESS -i f:\SQLBackups\ExpressMaint.sql -v DB=ksuite -v OPTYPE=DB -v BACKUPFOLDER=f:\SQLBackups -v REPORTFOLDER=f:\SQLBackups\Reports -v DBRETAINUNIT=days -v DBRETAINVAL=7"

Sqlcmd's parser probably does not ike the way that Powershell is stripping outthe quotes around some of the parameters like the query location and the way the DB="ksuite" parameter is seriously changed.

One option that might work is to just wrap the whole lot in single quotes to preserve the internal double quoted strings and hope that the sqlcmd parser can deal with this:

sqlcmd '-S .\SQLEXPRESS -i "f:\SQLBackups\ExpressMaint.sql" -v DB="ksuite" -v OPTYPE="DB" -v BACKUPFOLDER="f:\SQLBackups" -v REPORTFOLDER="f:\SQLBackups\Reports" -v DBRETAINUNIT="days" -v DBRETAINVAL="7"'

That will deliver the unmodified parameter list to sqlcmd but it does so as a single parameter as far as the shell is concerned and I would be surprised if it worked. You are much more likely to have success if you selectively quote all parameter blocks that are being modified with single quotes as follows:

sqlcmd.cmd -S .\SQLEXPRESS -i '"f:\SQLBackups\ExpressMaint.sql"' -v 'DB="ksuite"' -v 'OPTYPE="DB"' -v 'BACKUPFOLDER="f:\SQLBackups"' -v 'REPORTFOLDER="f:\SQLBackups\Reports"' -v 'DBRETAINUNIT="days"' -v 'DBRETAINVAL="7"'

If this still doesn't work there may be other items that need to be explicitly escaped and not just avoided by switching between single and double quoting by using the backtick character which is used as the escape character in Powershell string parsing.

You can get more info on all aspects of quoting and escaping via help about_quoting from within PowerShell.