Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.