sqlcmd. How to pass in a variable that includes a colon and a space?

Question

I've been struggling with this for a while now. I'm trying to invoke a sql script and pass two variables into it using sqlcmd. I'm doing all this in PowerShell.

Here's what I've got:

$time = '12:00 AM'
$date = '06/20/2014'
$result = sqlcmd -U username -P password -i "c:\path\to\script.sql" -v date=$date -v time=$time

This fails with the following error message:

sqlcmd : Sqlcmd: 'time=12:00 AM': Invalid argument. Enter -? for help.

After some experimentation, I've discovered that the problem is the colon and the space in $time. If I remove the colon and the space $time = '1200AM', the command executes without any error.

Unfortunately, the script that I'm executing wants the exact format "12:00 AM".

Things that I've tried that didn't work:

$time="12\:00\ AM"
$time="12\\:00\\ AM"
$time="12"+":00"+" AM"
$time="12"+":00"
$time="12"+":"+"00"

These all respond with similar Invalid argument failures. The last few attempts were the solution from this similar post. They don't work.

I have also tried placing the string values directly in the sqlcmd invocation, like so:

$result = sqlcmd -U username -P password -i "c:\path\to\script.sql" -v date=$date -v time="12\:00\ AM". 

No dice, and anyways, I need to read the time in from somewhere else, so I need the $time variable.

Answer 1

I (finally) found a solution that worked for sqlcmd from a Powershell script. (Using invoke-sqlcmd was not an option for me)

I needed to pass an absolute path containing a colon in a variable (e.g., C:\rootdir\subdir). This worked from a regular command prompt, but I couldn't get it to work from a Powershell script. I came up with an ugly kludge, passing the parts before and after the colon in two variables, then reassembling it in the SQL script.

But then it failed when the path contained a space (e.g., C:\root dir\subdir).

So I finally found a solution that fixed both colons and spaces. It involved enclosing the path text in double quotes, then enclosing the double-quoted path text in an outer set of single quotes. After building the full sqlcmd in a variable, it looked something like this:

SQLCMD <other args>  -v RootPath='"C:\root dir\subdir"'

(That's an outer set of single quotes (') and an inner set of double quotes (")).

This also worked if the path didn't have a colon, e.g., \\nodename\root dir\subdir. This had been a problem when I tried to split the path around an assumed colon. I'm still not sure why both outer single quotes and inner double quotes are necessary, but that was the only version that worked for me.

ADDENDUM: This only worked for Powershell 5, and broke when my script was run from Powershell 4. To make it work on both, I found I needed to enclose internal spaces in single quotes, e.g.,

SQLCMD <other args>  -v RootPath='"C:\root' 'dir\subdir"'