可以从命名管道/fifo中读取Sql Server批量插入吗? [英] Can Sql Server BULK INSERT read from a named pipe/fifo?

问题描述

BULK INSERT/bcp是否可以从 fifo 样式的命名管道读取?

Is it possible for BULK INSERT/bcp to read from a named pipe, fifo-style?

也就是说，不是从真实的文本文件中读取，而是可以使BULK INSERT/bcp从另一个进程的写入端上的命名管道读取吗?

That is, rather than reading from a real text file, can BULK INSERT/bcp be made to read from a named pipe which is on the write end of another process?

例如:

1. 创建命名管道
2. 将文件解压缩到命名管道
3. 使用bcp或BULK INSERT从命名管道读取

或:

1. 创建4个命名管道
2. 将1个文件拆分为4个流，将每个流写入单独的命名管道
3. 从4个命名管道中读取带有bcp或BULK INSERT的4个表

我找到的最接近的是这位研究员(站点现在无法访问)，他设法用自己的实用程序和用法将写入 w/bcp命名管道，

The closest I've found was this fellow (site now unreachable), who managed to write to a named pipe w/ bcp, with a his own utility and usage like so:

start /MIN ZipPipe authors_pipe authors.txt.gz 9
bcp  pubs..authors out  \\.\pipe\authors_pipe -T -n

但是他无法扭转局面.

But he couldn't get the reverse to work.

因此，在我开始愚弄人的事情之前，我想知道从根本上是否有可能从带有大容量插入或bcp的命名管道中读取 .如果可能的话，如何设置呢? NamedPipeServerStream或.NET System.IO.Pipes命名空间中的其他内容是否足够?

So before I head off on a fool's errand, I'm wondering whether it's fundamentally possible to read from a named pipe w/ BULK INSERT or bcp. And if it is possible, how would one set it up? Would NamedPipeServerStream or something else in the .NET System.IO.Pipes namespace be adequate?

例如，使用Powershell的示例:

eg, an example using Powershell:

[reflection.Assembly]::LoadWithPartialName("system.core")
$pipe = New-Object system.IO.Pipes.NamedPipeServerStream("Bob")

然后....什么?

推荐答案

我已成功使BULK INSERT(但不是BCP)与Windows 7和SQL Server 2008R2上的命名管道一起正常使用.有一些技巧.

I have succeeded in getting BULK INSERT (but not BCP) to work correctly with named pipes on Windows 7 ans SQL Server 2008R2. There are some tricks.

首先，我必须在两个不同的线程(都具有相同的管道名称)上创建两个命名管道实例. SQL Server将打开第一个实例，从中读取几个字节，然后将其关闭，从而导致WriteFile在第一个线程中引发PipeException.然后，SQL Server将立即重新打开命名管道，并从中传输所有数据.如果我没有第二个线程在后台准备服务数据，那么SQL服务器将在我的第一个线程有时间从PipeException中恢复之前返回错误.

First, I had to create two named pipe instances on two different threads, both with the same pipe name. SQL Server would open the first instance, read a few bytes from it, and close it, causing WriteFile to raise a PipeException in the first thread. SQL Server would then immediately reopen the named pipe, and stream in all of the data from it. If I didn't have a second thread sitting in the background ready to serve the data, SQL server would return an error before my first thread had time to recover from the PipeException.

第二，我必须在一次对WriteFile的调用中写入所有数据.我从一个循环开始，在该循环中我向管道写入了多个批处理，但是BULK INSERT仅使用我编写的第一个批处理.它似乎在进行非阻塞读取，并将所有返回零字节的读取都视为文件结尾.

Second, I had to write all of the data in a single call to WriteFile. I started with a loop where I wrote multiple batches to the pipe, but BULK INSERT only used the first batch that I wrote. It seems to do a non-blocking read, and treat any read that returns zero bytes as an end-of-file.

第三，必须将XML格式文件(如果使用)写入常规文件.我没有成功让SQL Server从管道读取格式文件.我不知道它是否可以从管道读取非XML格式的文件.

Third, an XML format file, if used, must be written to a regular file. I have not succeeded in getting SQL Server to read the format file from a pipe. I don't know if it can read a non-XML format file from a pipe.

这篇关于可以从命名管道/fifo中读取Sql Server批量插入吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！