7

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

3

3 Answers 3

3

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.

2

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

1

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
1
  • 2
    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, 2010 at 15:43

You must log in to answer this question.

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