1

I'm trying to use a Windows command prompt on Server 2012 R2 to run a batch file with the following command line. I want to run an SQL command (sqlcmd) and return the results to the console window.

This is what I'm currently trying, but sqlcmd keeps throwing back:

Sqlcmd: 'test': Invalid argument. Enter '-?' for help.

FOR /F "tokens=* USEBACKQ" %%F IN (`sqlcmd -S localhost -E -i "backup.sql" -v dbname="test"`) DO (
    Echo %%F
)

Note: I have also tried to run just this sqlcmd command (above) in a command prompt with no issues. It's like it does not like the FOR /F loop or something.

However if I try it without parameters/variables it works perfectly!

FOR /F "tokens=* USEBACKQ" %%F IN (`sqlcmd -S localhost -E -i "backup.sql"`) DO (
    Echo %%F
)

Does anyone know a way around getting the variables passed to my SQL query using sqlcmd, Windows CMD, as well as a FOR /F loop such as in my first example?

Mofi
  • 46,139
  • 17
  • 80
  • 143
Arvo Bowen
  • 4,524
  • 6
  • 51
  • 109

1 Answers1

2

When a line batch code is processed it goes through multiple phases, in a specific order. The order of these phases means that in this case your equals character, = requires special attention. For a full explanation on these phases read this information.

Here's two methods, one of which was already given in the comment area.

I have formatted the command over three lines so that it's clearer, whether you do that too is optional.

Escape the equals character = with a caret ^:

For /F "Delims=" %%A In ('
    sqlcmd -S localhost -E -i backup.sql -v dbname^=test
') Do Echo %%A

Or if you wish to doublequote your filename and value:

For /F "Delims=" %%A In ('
    sqlcmd -S localhost -E -i "backup.sql" -v dbname^="test"
') Do Echo %%A

Surround the command in doublequotes, ":

For /F "Delims=" %%A In ('
    "sqlcmd -S localhost -E -i backup.sql -v dbname=test"
') Do Echo %%A

 

For /F "Delims=" %%A In ('
    "sqlcmd -S localhost -E -i "back up.sql" -v dbname="my test""
') Do Echo %%A

Please note as shown above, whilst it's probably good practice, Microsoft state, 'File paths that contain spaces must be enclosed in quotation marks' and 'Enclose the value in quotation marks if the value contains spaces'. They were not needed in your case and could have been omitted.

Compo
  • 36,585
  • 5
  • 27
  • 39
  • It was 100% the carrot that got me. Thanks! – Arvo Bowen Mar 15 '18 at 23:44
  • To be honest I didn't even use double quotes around my entire command. I do however always try to use them around params when I can, except in rare situations such as robocopy that can freak out with slashes and double quotes. I actually use a fourth way... No double quotes around the entire command and double quotes around the param. – Arvo Bowen Mar 16 '18 at 12:18
  • @Avro, have added that additional scenario to my answer just for completeness. – Compo Mar 16 '18 at 12:24