How to set a sqlcmd output to a batch variable?
20,686
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
Author by
JohnN
BY DAY: I do advanced testing for software. I utilize batch scripts, SQL queries, excel, and some R programming in my daily routine. BY NIGHT: I'm an extreme sci-fi & fantasy nerd. I'm sure some of you have me beat though. I consider myself an expert on Star Wars Legends canon.
Updated on July 09, 2022Comments
-
JohnN almost 2 years
I'm trying to set the output of a sqlcmd query to a variable in a batch file.
Here's my query:
sqlcmd -S <SERVER> -d <DATABASE> -Q "select max(Column1)+1 from Table1"
This gives me exactly what I would 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'm guessing this is because the loop is setting the variable to the last line. So is there a way I could use tokens and delims to parse out the 10 instead?