How to run sqlcmd with parameters from batch and assign return value to a variable?

Question

I have an MS SQL script that takes two variables and does what its supposed to do, return a single value.

In my batch file, I call this script as follows and I get the output I expect.

sqlcmd -v ALM_Domain=%dom% ProjectName=%proj% /h-1 -S %DB% -E -C -i GetDBNameForDomainProject.sql

However, I need to use the value returned by the query in my subsequent processing, and decided I'd like to assign the return value into a variable. To do so, I used the following construct

for /F usebackq %%a IN (`sqlcmd -v ALM_Domain=%dom% ProjectName=%proj% /h-1 -S %DB% -E -C -i GetDBNameForDomainProject.sql`) do (
    set DBNAME=%%a
)

for which I get the following error:

Sqlcmd: 'NCA ProjectName SIM': Invalid argument. Enter '-?' for help.

Notice the '=' sign seems to have disappeared. Help for the for command states:

Finally, you can use the FOR /F command to parse the output of a command. You do this by making the file-set between the parenthesis a back quoted string. It will be treated as a command line, which is passed to a child CMD.EXE and the output is captured into memory and parsed as if it was a file.

If I attempt to use:

for /F %%a IN ('sqlcmd -v "ALM_Domain=%dom% ProjectName=%proj%" /h-1 -S %DB% -E -C -i GetDBNameForDomainProject.sql') do (
    set DBNAME=%%a
)

I get:

Sqlcmd: 'ALM_DOMAIN=NCA ProjectName=SIM': Invalid argument.

dbenham · Accepted Answer · 2015-04-06 12:59:10Z

Having nothing to do with your problem - USEBACKQ is rarely needed when executing a command with FOR /F. Its primary use is to enable reading from a file that contains spaces or poison characters in the name, which require quotes to preserve. But double quotes normally indicate a string value, hence the need for USEBACKQ

for /f "usebackq" %%A in ("file name with spaces.txt") do ...

If you are executing a command, then all you need are normal single quotes, without the USEBACKQ option.

for /f %%A in ('someCommand.exe') do ...

You certainly can use USEBACKQ with the backtick, but I see no benefit.

Now for your actual problem - you have noticed that the parser has converted your = into a <space>. This is a "feature" of the batch parser which must parse the command into a string that gets executed by CMD /C. The parser treats =, ,, ;, <tab>, <space>, and <0xFF> as token delimiters, and any contiguous string of token delimiters is transformed into a single <space>.

The solution is to either enclose the entire command within double quotes (inside the outer single quotes that signify a command), or escape the problem characters. In your case, I believe you can use the double quotes.

for /F %%a IN ('"sqlcmd -v ALM_Domain=%dom% ProjectName=%proj% /h-1 -S %DB% -E -C -i GetDBNameForDomainProject.sql"') do (
  set "DBNAME=%%a"
)

Not an issue with your case, but if your command already uses double quotes, then the alternative is to escape the =.

for /F %%a IN ('sqlcmd -v ALM_Domain^=%dom% ProjectName^=%proj% /h-1 -S %DB% -E -C -i GetDBNameForDomainProject.sql') do (
  set "DBNAME=%%a"
)

Thanks dbenham. You answered my question very clearly, and I tried it both ways with the double quotes inside the singles, and escaping the equals sign.

Collectives™ on Stack Overflow

How to run sqlcmd with parameters from batch and assign return value to a variable?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related