SQLCMD Invalid Argument – Troubleshooting Variable Passing Issues

scriptingsql-server-2016

I'm trying to use a DOS command prompt on Server 2012R2 to run a batch with the following in it. I want to run a SQL command (sqlcmd) and return the results to the command window.

This is what I'm currently trying but sqlcmd keeps throwing back Sqlcmd: 'test': Invalid argument. Enter '-?' for help.20:56:08.

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

However if I try it without params/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, DOS CMD, as well as a FOR /F loop such as in my first example?

Best Answer

In your command that works, you don't have an equal sign (=). When you run the command that fails and look at the output, you should notice the equal sign disappears. That's because that character needs to be escaped with a caret. (in my example below, notice the caret before the equal sign)

Try:

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

Related Solutions

Postgresql – Variable substitution in psql when using \copy

You can circumvent this limitation by piping the whole command to psql:

echo "\copy tbl FROM '/path/to/myfile.pgsql'" | psql mydb

SQLCMD Scripting – How to Pass Multiple Statements

There are a couple of problems with this approach:

The immediate problem is that you cannot continue an empty line. So the two lines (following each go ^ line) that are only the single character ^ are causing the line to end and not continue. It has nothing to do with the go. So you can just add a space before the carrot and it will build the full string.
However, even if you fix problem #1, you are still left with invalid SQL since GO needs to be on a line by itself or, at most, optionally followed by some whitespace and an INT value for how many times to repeat the prior batch (e.g. GO 12). But, using line continuation via ^ there won't be any embedded returns so everything will be submitted as a single line, and that is not valid syntax with the GO commands in there.

This leaves you with two options:

If you are submitting SQL that does not require any batch separation, then just get rid of the GO commands (and wouldn't hurt to get rid of the @ sign, and the ENABLEDELAYEDEXPANSION and final GO aren't needed either):

SET SQLSTRING=select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null; ^
 ^
ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; ^
ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; ^
 ^
select cast(DB_NAME(database_id) as char(30)), ^
        cast(mirroring_state_desc as char(15)) ^
from sys.database_mirroring ^
where mirroring_state_desc is not null;

ECHO.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -Q "%SQLSTRING%"

If you do need batch separation (e.g. you have a CREATE PROCEDURE statement or something like that), then you can create a SQL file in your CMD script, add each line individually, and then use that as the SQL script that SQLCMD will run. This allows you to embed returns as well as use GO commands:

@ECHO OFF

ECHO. > MultiLine.sql
ECHO select cast(DB_NAME(database_id) as char(30)), >> MultiLine.sql
ECHO        cast(mirroring_state_desc as char(15)) >> MultiLine.sql
ECHO from sys.database_mirroring >> MultiLine.sql
ECHO where mirroring_state_desc is not null; >> MultiLine.sql
ECHO GO >> MultiLine.sql
ECHO. >> MultiLine.sql
ECHO ALTER DATABASE MSCRM_CONFIG SET PARTNER SUSPEND; >> MultiLine.sql
ECHO ALTER DATABASE XeroxGHSCRMTest_MSCRM SET PARTNER SUSPEND; >> MultiLine.sql
ECHO GO >> MultiLine.sql
ECHO. >> MultiLine.sql
ECHO select cast(DB_NAME(database_id) as char(30)), >> MultiLine.sql
ECHO        cast(mirroring_state_desc as char(15)) >> MultiLine.sql
ECHO from sys.database_mirroring >> MultiLine.sql
ECHO where mirroring_state_desc is not null; >> MultiLine.sql

ECHO.
sqlcmd -S AGABQ0VMCRMDB01\PROD_CRM -E -i MultiLine.sql

Best Answer

Related Solutions

Postgresql – Variable substitution in psql when using \copy

SQLCMD Scripting – How to Pass Multiple Statements

Related Question