sql server - Using SQLCMD in a batch file, how can I parse the response and check for an error? -

i'm using sqlcmd count of rows in table, want aware if query hits error.

the sqlcmd i'm using looks this:

sqlcmd -s %server% -u %user% -p %pass% -b -q "select count(*) %table%" 

if works, return:

-----------       10205  (1 rows affected) 

(note, there blank line above ------- column name i'm not specifying.)

if pass in table doesn't exist, following response:

msg 208, level 16, state 1, server devserver, line 1 invalid object name 'dbo.no_table'. 

since have -b flag, can check errorlevel value (in case, 1).

to store count variable, i've been using following line:

for /f %%i in ('sqlcmd -s %server% -u %user% -p %pass% -b -q "select count(*) %table%" ^| findstr /r "[^(][0-9]"') set /a rec_count=%%i 

after for, %errorlevel% returns 0. inside do, errorlevel 0.

is there simple way run sqlcmd, store count if there not error, , print both lines if there error?

commands executed /f implicitly executed via new cmd session. example, for /f %a in ('echo hello') ..., command executed becomes c:\windows\system32\cmd.exe /c echo hello.

your command setting errorlevel, value lost child cmd session terminates , control returned batch script.

so /b option not doing you, , can dropped.

you can suppress header info adding -h -1 option.

you can suppress (1 rows affected) message prefixing command set nocount on;

you can add -r 1 option cause error messages appear on stderr instead of stdout. prevent /f processing error, , error message appear on screen instead.

you can clear rec_count variable before execute command. remain undefined if there error, else contain count if there no error.

set "rec_count=" /f %%a in (   'sqlcmd -s %server% -u %user% -p %pass% -h -1 -r 1 -q "set nocount on;select count(*) %table%"' ) set "rec_count=%%a" if not defined rec_count echo there error! 

one other thing might consider using environment variables recognized sqlcmd server, username, , password. won't have use -s, -u, or -p options. handy if batch script runs many sqlcmd commands.

set "sqlcmdserver=yourserver" set "sqlcmduser=yourusername" set "sqlcmdpassword=yourpassword"  set "rec_count=" /f %%a in (   'sqlcmd -h -1 -r 1 -q "set nocount on;select count(*) %table%"' ) set "rec_count=%%a" if not defined rec_count echo there error!