9

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?

JohnN
  • 968
  • 4
  • 13
  • 35
  • I'm not 100% sure, but [this SO post may help](http://stackoverflow.com/questions/2014129/is-there-a-way-to-suppress-x-rows-affected-in-sqlcmd-from-the-command-line) – Ed Gibbs Dec 23 '14 at 16:35
  • Thank you, I didn't see that question when I was searching. – JohnN Dec 23 '14 at 16:39
  • 1
    Hello, this won't work unless you put the do command on the same line as the for loop – Neil Apr 09 '20 at 18:02

1 Answers1

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
Stephan
  • 53,940
  • 10
  • 58
  • 91
Dave.Gugg
  • 6,561
  • 3
  • 24
  • 43
  • 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! – JohnN Dec 23 '14 at 16:38
  • I am storing the sql command in a batch variable; Can you suggest me how can i do this with my variable? – Praveen Patel G Sep 24 '19 at 12:15