How to set sqlcmd output to batch variable?

Question

I am trying to set the sqlcmd query output for a variable in a batch file.

Here is my request:

sqlcmd -S <SERVER> -d <DATABASE> -Q "select max(Column1)+1 from Table1"

This gives me what I expect and what I want:

-----------
         10
<1 rows affected>

However, when I try to set it to a variable, I used this script:

for /f %%a in ('sqlcmd -S <SERVER> -d <DATABASE> -Q "select max(Column1)+1 from Table1"') 
    do set ColumnVar=%%a
echo %ColumnVar%
pause

This gives me this result instead: <1 rows affected>I assume this is because the loop sets the variable to the last line. So is it possible to use tokens and delimits for analysis 10 instead?

Answer 1

Try turning on NOCOUNT:

for /f %%a in ('sqlcmd -S <SERVER> -d <DATABASE> -Q "SET NOCOUNT ON; select max(Column1)+1 from Table1"') 
    do set ColumnVar=%%a
echo %ColumnVar%
pause