Batch - parse multi-line command output in variable

Question

I have a batch I use to run multiple SQL scripts and record their output in a single text file (SQL_results.txt). This is the block that will run a script and check the results for errors at the beginning of the output (errors will always be on a single line, starting with "Msg"):

SQLCMD command_to_run_a_script > error.txt
set /p result=<error.txt
set result=%result:~0,3%
if ""%result%""==""Msg"" exit
type error.txt >> SQL_results.txt
del error.txt

It is worth noting that the output has a dynamic number of lines and may exceed the variable char limit and that the "Msg..." line may not always be the first.

I tried this for a test of doing the check in a variable, but it will only write the last line of the output for each line:

for /f "delims=" %%i in ('SQLCMD command_to_run_a_script') do (set VAR=%%i & ECHO %VAR%>> SQL_results.txt)

Could something like this be done by parsing the results line-by-line in trying to find one that begins with "Msg", in a variable instead of having to create & delete a text file for each script?

You know, I bet sqlcmd will exit zero on success, non-zero with errors. Check the documentation to be sure. But if that's true, you could just sqlcmd command_to_run_a_script || echo There was an error. or similar. If the exit code is unreliable, you could also sqlcmd command_to_run_a_script | findstr /i "^Msg" >NUL && echo Output contained an error. Something went wrong. — rojo
– rojo, Commented Aug 3, 2017 at 0:55
Thank you for pointing that out, SQLCMD will indeed exit on error if the -b switch is used, so I can work with this. — VladP
– VladP, Commented Aug 3, 2017 at 1:57

user6811411 · Accepted Answer · 2017-08-03 00:10:39Z

1

As a variable will hold only one line it's not quite clear to me what you intend.

To filter only lines with errors into a file:

SQLCMD command_to_run_a_script|Findstr "^MSG" > error.txt

Store MSG lines in an array and output at the end

@Echo off&Setlocal EnableDelayedExpansion
Set cnt=0
For /f "delims=" %%A in (
  'SQLCMD command_to_run_a_script ^|Findstr "^MSG" '
) Do (
  Set /A cnt+=1
  Set "MSG[!Cnt!]=%%A"
)
Echo had %cnt% MSG lines
For /l %%C in (1,1,%cnt%) Do Set MSG[%%C]

answered Aug 3, 2017 at 0:10

user6811411

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

1 Comment

VladP Over a year ago

The idea is to record all the results for all the scripts in the SQL_results.txt file. When an error occurs, this should also get recorded, but then the batch should stop running. rojo's comment changed the way I ran the scripts and got me to the desired result, but thank you for your help

Collectives™ on Stack Overflow

Batch - parse multi-line command output in variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related