How to set a sqlcmd output to a batch variable?

Question

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?

Hello, this won't work unless you put the do command on the same line as the for loop — Neil
– Neil, Commented Apr 9, 2020 at 18:02

Stephan · Accepted Answer · 2020-04-10 08:37:28Z

10

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

edited Apr 10, 2020 at 8:37

Stephan

56.7k11 gold badges66 silver badges97 bronze badges

answered Dec 23, 2014 at 16:36

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JohnN Over a year ago

That works perfect. Only one thing I had to modify was change "delims=" to "delims= " to get just the numerical output. Thank you kind sir!

Praveen Patel G Over a year ago

I am storing the sql command in a batch variable; Can you suggest me how can i do this with my variable?

Collectives™ on Stack Overflow

How to set a sqlcmd output to a batch variable?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related