How to run a sql script file using sqlcmd and output to both shell and file

Question

I'm trying to run a sql script from a file using sqlcmd, using the following command:

sqlcmd -S <server> -d <database> -i <input file> -o <output file> 
      -U <user> -P <password>

I run my sql file and output to a log file.

The problem is this change the output of sqlcmd to the file.. and i want to get the output also to the shell.

Solution 1

@Noam,

There's a standard program in UNIX world that reads from stdin and writes to stdout and to a specified file: the tee command. The following example lists the current directory to stdout and to "myfile":

$ ls | tee "myfile"

There are some open-source ports for windows, like wintee, and it's even trivial to make your own implementation (see here), but PowerShell actually has already this utility built-in: the Tee-Object.The following example is analogous the previous, in PowerShell:

PS [c:\tmp]
> dir | tee myDirContents.txt

Hence, the following command will execute myscript.sql into myserver, using current windows credentials and its result will be output either to the console and to "result.txt":

PS [c:\tmp]
> sqlcmd -i mysrcipt.sql -S myserver -E | tee "result.txt"

Solution 2

@Noam, albeit I couldn't find a way of duplicating the STDOUT handler, one solution that maybe solve you problem is to execute the sql script into the output file (i.e. result.txt) and then use type result.txt to print it into STDOUT.

c:>sqlcmd -i mysrcipt.sql -o result.txt -S myserver -E
c:>type result.txt

If you really need to duplicate the handler (maybe the command takes lots of time and you don't want to wait until it finishes before getting the first rows), you can make a simple program to copy the inputs from STDIN into STDOUT and also into a file. Then you could rewrite the command as:

c:>sqlcmd -i mysrcipt.sql -S myserver -E > duplicate.exe "result.txt"

Comments

[ExploringApple]

-U & -P is for server or database?