SQLCMD. How can I pass a variable containing a colon and a space?
I've been struggling with this for a while. I am trying to call a sql script and pass two variables into it using sqlcmd. I do all this in PowerShell.
Here's what I have:
$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
The following error message failed:
sqlcmd : Sqlcmd: 'time=12:00 AM': Invalid argument. Enter -? for help.
After some experimentation, I found that the problem is the colon and space in $ time. If I remove the colon and space $time = '1200AM'
, the command runs without any errors.
Unfortunately, the script I am running requires the exact "12:00 AM" format.
Things 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"
They all respond with similar errors Invalid argument
. The last few attempts were the solution from this similar post . They do not work.
I have also tried to place string values directly in the sqlcmd call like:
$result = sqlcmd -U username -P password -i "c:\path\to\script.sql" -v date=$date -v time="12\:00\ AM".
There are no cubes and anyway I need to read the time elsewhere, so I need the $ time variable.
source to share
Ok I figured out the solution. Hopefully it will be useful to other people somewhere along the way.
I switched from sqlcmd to Powershell Invoke-Sqlcmd. This STILL gave me problems, so I had to play around with it a bit. Here is my final result.
# import Invoke-Sqlcmd
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$time = "12:01 AM"
$date = "07/22/2014"
$datetime = "time='$time'", "date='$date'" # save to $datetime as an array
$result = Invoke-Sqlcmd -Username username -Password password -InputFile "c:\path\to\sql\script.sql" -Variable $datetime
Please note that the following DOES NOT WORK:
$datetime = "time='"+$time+"'", "date='"+$date+"'"
This was the first thing I tried and it resulted in an invalid argument being thrown.
source to share
I (finally) found a solution that worked for sqlcmd from a Powershell script. (Using invoke-sqlcmd was not there for me)
I needed to pass an absolute path containing a colon in a variable (e.g. C: \ rootdir \ subdir). It worked from a normal command line, but I couldn't get it to work with the Powershell script. I came up with an ugly kludge, passing the parts before and after the colon with two variables, and then put it back together in a SQL script.
But then it failed when the path contained a space (e.g. C: \ root dir \ subdir).
So, I found a solution that fixed both colons and spaces. It included enclosing the path text in double quotes, and then enclosing the double quoted text in an outer set of single quotes. After creating the complete sqlcmd in a variable, it looked something like this:
SQLCMD <other args> -v RootPath='"C:\root dir\subdir"'
(This is an outer set of single quotes (') and an inner set of double quotes (")).
It also worked if there was no colon in the path, for example \\ nodename \ root dir \ subdir. This was the problem when I tried to split the path around the intended colon. I'm still not sure why both outer single quotes and inner double quotes are needed, but this was the only version that worked for me.
ADDITION: This only worked for Powershell 5 and broke when my script was run from Powershell 4. To make it work on both I found that I needed to enclose the inner spaces in single quotes, for example
SQLCMD <other args> -v RootPath='"C:\root' 'dir\subdir"'
source to share
I modified your PowerShell script a bit by adding spaces before and after each variable assignment statement like this:
$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
It works for me (tested on PowerShell 2.0).
source to share
I'll try first -v "date=$date" -v "time=$time"
.
If that didn't work, I would try the following:
$time = '12:00 AM'
$date = '06/20/2014'
$time = 'time=' + $time
$date = 'date=' + $date
$result = sqlcmd -U username -P password -i "c:\path\to\script.sql" -v $date -v $time
When PowerShell runs the program, it parses the string, resolves any PowerShell in it, and then tries to execute the string. As a result, it feels like you need an extra layer of abstraction that you really don't see in traditional shell scripts or batch scripts.
source to share