Sql Server 2005 - sqlcmd with output to shell and file

Question

I do some command line batch (.bat) with sqlcmd as this way:

sqlcmd -i Scripts\STEP01.sql -o PROCESS.log -S MYSERVER -E -d MYDATABASE

and i need an output file (it's works currently) and also the output trought the screen to do something like:

@echo off
echo The result of the query was:
    sqlcmd -i Scripts\STEP01.sql -o PROCESS.log -S MYSERVER -E -d MYDATABASE
pause
CHOICE /C:YN /M "Is the result accord?"
IF ERRORLEVEL 2 GOTO ENDWITHERROR
IF ERRORLEVEL 1 GOTO STEP2

Note:

• Yes, the script works suscefull, it's not an issue question.
• And yes, I've a "print "something" on my sql. The log file gets the output.

Thanks a lot!

Answer 1

In order to implement what you want you need something functionally equivalent to tee or grep on unix. Windows does ship with a utility called find.exe that has similar functionality to grep, so you may be able to use that to extract relevant lines from your output file. As far as I am aware there is no equivalent to tee shipped with base windows builds.

This sort of thing is trivial in a unix shell scripting environment but can cause issues on a raw Windows build. If you can't implement what you want using find.exe, three basic approaches come to mind:

1. cmd.exe actually has significant pipelining capabilities, although not as rich as a unix shell. You can get Win32 ports of tee and grep from the GNU Win32 collection, or from Microsoft Services for Unix (SFU). The GNU Win32 ones are better because they use msvcrt.dll and understand native Windows paths. SFU places an emulation layer over the base O/S in much the same way as cygwin, so it emulates /cygdrive/c style paths that behave in a more unix-like manner.

   An alternative to this would be to use WSH, powershell or even a small .Net console app to implement a small utility to read the output from the log file and present your prompt to the user.

2. Reimplement the process in a way that allows the control to be automated. If you can agree business rules for this control it can be run automatically and simply log the result, generating a status report when the process has finished running.

3. It seems quite strange that you are developing a server-side process that requires interactive input from an operator. If this process has to be run interactively, is there any reason why the interactive parts could not simply be run from somebody's PC? This would probably allow you considerably more flexibility in installing third party components or bespoke client-side code.