Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
1  
Why are the simplest things in Microsoft world nearly impossible. – PP. Feb 3 '10 at 10:28
1  
Because from a "majority of end-user customer base", this isn't in the "simple things I want to do list". Windows has different priorities. It obviously did something right with such a big push in Linux to improve it's GUI to catch up with Windows... Btw, I'm a long time Linux user and I hate windows – Dan McGrath Feb 4 '10 at 11:12
up vote 1 down vote accepted

On Unix you can use mkfifo for this sort of thing, but as far as I know, there's no Windows command-line tool to create or manipulate named pipes. They're not accessible to command-line tools in the way that conventional and UNC paths are.

You can create a named pipe in Perl by using Win32::Pipe, if you know Perl well enough. And you could write a Perl client to pull data out of the pipe and send it to STDOUT, but this really can't be done elegantly in Windows.

share|improve this answer

I doubt you can do that, or even get it to work. But wouldn't it just be simpler to do this in two steps?

bcp MyDatabase..MyTable OUT c:\temp\dataset.csv -c -t,
gzip c:\temp\dataset.csv
share|improve this answer
1  
Yes, it would be easier, but also carries the risk of using up too much local disk space, and unnecessarily writes and re-reads data. It's an inefficient thing to do. – PP. Feb 10 '10 at 15:43

I was just searching for the same thing!

This guy seems to have accomplished bcp to gzip w/ a background job:

http://jcarlossaez.spaces.live.com/blog/cns!B3378F057444B65C!108.entry

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .