1

I'm trying to export to a Windows Named Pipe from the SQL Server bcp utility.

I'm creating my pipe on Windows using VB.Net as follows:

Dim ps As New PipeSecurity()
ps.AddAccessRule(New PipeAccessRule("Users", PipeAccessRights.FullControl, System.Security.AccessControl.AccessControlType.Allow))

Dim pipeServer As New NamedPipeServerStream("testpipe", PipeDirection.InOut, 4, PipeTransmissionMode.Byte, PipeOptions.None, 131072, 131072, ps)

pipeServer.WaitForConnection()

I'm running a Teradata FastLoad job to start reading from the pipe. I get the message "Starting to send to RDBMS with record 1". This tells me the utility has connected to the pipe and is waiting to be fed records. IsConnected on the pipe also returns true.

When I run bcp however I get this error:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file

Here is my bcp commmand line...

"bcp" [SQL_Class].[dbo].[Customer_table] out "\\.\pipe\testpipe" -S 12.12.122.12,12121 -U sa -e "err.txt" -o "out.txt" -w

Any suggestions on what I might be doing wrong? Is this even supported (I've seen different answers online)? Should I be using something else?

Also, when not using bcp if I just try to run this command...

type data.txt > \.\pipe\testpipe

I get an error saying all instances are busy so this is pointing to the pipe and not bcp.

Any help would be appreciated. Thank you.

Todd Wilson
  • 71
  • 5
  • Perhaps you could install SQL Express in a different host, enable named pipes only and ensure it can communicate (both the DOS command and BCP). BCP _should_ be able to talk to SQL Express over named pipes. This link http://stackoverflow.com/questions/2197017/can-sql-server-bulk-insert-read-from-a-named-pipe-fifo says that BCP tries to take an exclusive lock on the file, and I guess Teradata is stopping it. Have you considered just using an intermediate flat file or a linked server to transfer the data? – Nick.Mc Aug 24 '14 at 05:04
  • Thank you ElectricLlama. This is good information. Unfortunately, for me the requirement is to use named pipes and it has to be done in .NET so I'm unable to use any outside tools such as SQL Server Management Studio. It looks like we could create our own linked server in our tool, but we need to move data as fast a possible and I'm not sure that doing this will provide the performance we are looking for. We also have the bcp already in our tool so we'd like to see if these named pipe will work before developing another solution. Thanks again for your help. – Todd Wilson Aug 25 '14 at 03:47
  • Perhaps you'll have more control if you leave BCP out of the equation. Since you must use .Net just write directly to the named pipe in your .Net code (somehow - I don't know how). Perhaps you can have more control over it or at least get a better error message. Seems like you're in an unfortunate situation since you are confined to certain technologies but also have a limited timeframe. – Nick.Mc Aug 25 '14 at 04:32
  • I've been able to write to the pipe just using command line: – Todd Wilson Aug 25 '14 at 10:32
  • "type data.txt > \\.\pipe\testpipe". The data loaded so I know the issue is with bcp writing to the pipe. We were hoping to be able to do this at a block-level using the bcp utility although I guess it doesn't have to be bcp. We just wanted to use this because we already have a wizard in place for this. I'm really just looking for the fastest way to export data from SQL Server to a windows named pipe. It is surprising that Teradata supports Windows Named Pipes, but it appears Microsoft does not at least for bcp. – Todd Wilson Aug 25 '14 at 10:39
  • Is it just that BCP can't write to the named pipe because it needs exclusive access (whereas `type` does not?). Sorry I can't help further, it's quite intriguing. – Nick.Mc Aug 25 '14 at 11:32
  • When I was creating the pipe I tried setting different pipe access rules because I thought it might be a permission issue, but I just think Windows Named Pipes are not supported now. Hopefully someone will prove me wrong. I'm looking into some other options now such as SSIS. Thank you for your help ElectricLlama! – Todd Wilson Aug 25 '14 at 15:40

0 Answers0