I want to write a large dataset from a SQL Server 2005 database using BCP (bulk copy program).
Ideally I'd like to do the following:
bcp MyDatabase..MyTable OUT STDOUT -c -t, |gzip -c c:\temp\dataset.csv.gz
However BCP just writes this to a literal file named STDOUT.
I also tried:
bcp MyDatabase..MyTable OUT CON: -c -t, |gzip -c c:\temp\dataset.csv.gz
but this returns and error of
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file.
So, can I create a named pipe anywhere? I've seen hints on the web of somehow starting
gzip with one end tied to a filename like \\.\named_pipe - but how is this done?
Update: Note that Microsoft themselves acknowedge that they really don't care about efficiency with SQL Server: http://connect.microsoft.com/SQLServer/feedback/details/337702/support-bcp-out-in-and-bulk-insert-compressed-or-named-pipe-support-at-least