19

I'm trying to get the value of a SQL statement when I run it in a DOS batch file ...

sqlcmd -E -S DEVSERVER -Q "SELECT  COUNT(1) as [CaseCount] FROM Cases"

I'm not after the error level as in this stackoverflow question, rather I'm after the actual count returned from the database, so I can do some additional logic.

Improve this question

3 Answers 3

Reset to default
22

You can easily save the result of the execution into a text file, either by using the -o sqlcmd flag or by using the standard > redirector. You can then format this file by removing the column header (flag -h) and removing the rowcount from SQL Server (SET NOCOUNT ON).

The following script will generate a file result.txt with only the value of COUNT(1) and a line break:

SQLCMD -E -S devserver -Q "SET NOCOUNT ON; SELECT COUNT(1) FROM Cases" -h -1 
  > result.txt

And then read the value back with ...

set /p value=< result.txt
echo %value%
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

+1 Just saw, after posting my answer that your's also includes the -h -1 option.
Partly ... how do I get the value back into the DOS batch file ?
Then maybe @Christian.K solution (using FOR) might suits you better, since it will evaluate the result of the command and then you can assign to a batch variable, you only have to add a simple test to avoid the empty string.
I've tracked down a solution and added to your post ... thanks for the response
reading the value from the file with set /p value=<result.txt is great.
19

You can avoid an extra/temporary file, by calling sqlcmd.exe using the FOR batch command:

for /F usebackq %%i in (`sqlcmd -E -S "devserver,4711" -h-1 -Q "SET NOCOUNT ON; SELECT COUNT(1) ..."`) do (
    set count=%%i
)

if not defined count (
    echo Failed to execute SQL statement 1>&2
) else (
    echo %count%
)

Some notes:

  • If called from an interactive CMD.EXE session, i.e. the command line, use %i instead of %%i
  • The -h-1 option tells sqlcmd.exe to suppress the column headers, so the output is really only one line of text.
  • I used a fictional port 4711 with the server, to show that you need to put the whole "server,port" specification in double quotes. Otherwise, due to CMD's command line parsing rules, sqlcmd would see server and port as to distinct arguments and fail.
Improve this answer

2 Comments

Sometimes, there might be leading or trailing spaces in the result so you might want to use for /F "delims= " %%i... instead. Do note that this deletes all spaces including the ones in between so be careful.
@Mark OK, I see. In that case it might be better to trim the spaces off the count variable, after the for look: set count=%count: =%.
4

The answer from @GerardoLima was 90% of the way there. You additionally need to evaluate the input value with set /a to strip the leading whitespace.

This is not required with the answer from @Christian.K

SQLCMD -E -S devserver -Q "SET NOCOUNT ON; SELECT COUNT(1) FROM Cases" -h -1 > result.txt
set /p raw=< result.txt
echo '%raw%'
set /a value=%raw%
echo '%value%'

My output was

'          0'
'0'
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.