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.
Alright, I figured a solution out. Hopefully it will be useful to other people somewhere down the road.
I switched from sqlcmd to Powershell's Invoke-Sqlcmd. This STILL gave me problems, so I had to fiddle around with it a little. Here's my end 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
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 exception.