BCP with named pipes

i have a SQL Server 7.0 and SQL Server 2000 running on 1 machine. The 7.0
version is the default instance.( obviously 2000 version will be a named
instance)
i want to use BCP to import data into a table in the SQL Server 2000
instance using named pipes. According to the docs if u don't specify the
server name with -S, named pipes are used.
if i do a "bcp table1 in datafile -U user -P user -c" i get an
error as:
SQLState = 08001, NativeError = 17
Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
not ex
ist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Conn
ect()).
My guess is that it is trying to connect to the default instance (SQL Server
7.0 which is not running, since i don't use that) and fails.
So how can i connect to a named instance with BCP using named pipes
thanks
Vivek T S
Member Technical Staff (Inucom)
I'm not sure I completely understand your question.
Is there a reason you can't use the -S option?
-S<servername\instance_name>
Rick Sawtell
MCT, MCSD, MCDBA
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:A43D4B3A-4404-4D3A-A12A-6436ABE88FE2@.microsoft.com...
> i have a SQL Server 7.0 and SQL Server 2000 running on 1 machine. The 7.0
> version is the default instance.( obviously 2000 version will be a named
> instance)
> i want to use BCP to import data into a table in the SQL Server 2000
> instance using named pipes. According to the docs if u don't specify the
> server name with -S, named pipes are used.
> if i do a "bcp table1 in datafile -U user -P user -c" i get
an
> error as:
> SQLState = 08001, NativeError = 17
> Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does
> not ex
> ist or access denied.
> SQLState = 01000, NativeError = 2
> Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
> (Conn
> ect()).
>
> My guess is that it is trying to connect to the default instance (SQL
Server
> 7.0 which is not running, since i don't use that) and fails.
>
> So how can i connect to a named instance with BCP using named pipes
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)
|||when u use the -S servername option, even though u r in the same machine
tcp/ip will be used for the data transfer. Whereas named pipes (i.e. shared
memory)
is the fastest way to transfer data when u r doing a bcp on the same machine
as the server. to make use of the named pipes, the docs say that, -S option
should be dropped. but i don't know how i can make this work with multiple
instances on my machine.
eg: on a machine with a single instance of SQL Server:
a) bcp table in datafile -U user -P passwd (should work and named
pipes will be used)
b) bcp table in datafile -S servername -U user -P passwd (in this case
tcp/ip will be used)
But on a machine with multiple instances of SQL Server how (a) above work is
my doubt.
thanks
"Rick Sawtell" wrote:

> I'm not sure I completely understand your question.
> Is there a reason you can't use the -S option?
> -S<servername\instance_name>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:A43D4B3A-4404-4D3A-A12A-6436ABE88FE2@.microsoft.com...
> an
> Server
>
>
|||I see...
Well if it's speed that you are looking for how about:
bcp out from the 7.0 server and then use BULK INSERT on the 2000 instance.
Bulk Insert in most cases is going to be faster than bcp anyhow. It's not
much, but ...
HTH
Rick Sawtell
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:0F5062CB-BA46-44DB-A4B1-B879C879F385@.microsoft.com...
> when u use the -S servername option, even though u r in the same machine
> tcp/ip will be used for the data transfer. Whereas named pipes (i.e.
shared
> memory)
> is the fastest way to transfer data when u r doing a bcp on the same
machine
> as the server. to make use of the named pipes, the docs say that, -S
option
> should be dropped. but i don't know how i can make this work with multiple
> instances on my machine.
> eg: on a machine with a single instance of SQL Server:
> a) bcp table in datafile -U user -P passwd (should work and
named
> pipes will be used)
> b) bcp table in datafile -S servername -U user -P passwd (in this case
> tcp/ip will be used)
> But on a machine with multiple instances of SQL Server how (a) above work
is[vbcol=seagreen]
> my doubt.
> thanks
>
> "Rick Sawtell" wrote:
7.0[vbcol=seagreen]
named[vbcol=seagreen]
the[vbcol=seagreen]
get[vbcol=seagreen]
does[vbcol=seagreen]
Memory]ConnectionOpen[vbcol=seagreen]
|||If you are running BCP on the server it will by default use Shared Memory,
based on your Client Network settings. (See Client Network Utility)
If you want to use Named Pipes for your local server connection there are
two options:
1) Use a client alias, created with the Client Network Utility and use the
name of the alias in the -S parameter
2) Use a protocol prefix as described in
http://sqldev.net/faq.htm#FAQ:%20How...ork%20settings
Like bcp testdb.dbo.loadsrc out loadtest.bcp -S np:gertd00\sql80 -T -n
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Rick Sawtell" <ricksawtell@.hotmail.com> wrote in message
news:%23k5rTpPkEHA.556@.tk2msftngp13.phx.gbl...
>I see...
> Well if it's speed that you are looking for how about:
> bcp out from the 7.0 server and then use BULK INSERT on the 2000 instance.
> Bulk Insert in most cases is going to be faster than bcp anyhow. It's
> not
> much, but ...
> HTH
> Rick Sawtell
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:0F5062CB-BA46-44DB-A4B1-B879C879F385@.microsoft.com...
> shared
> machine
> option
> named
> is
> 7.0
> named
> the
> get
> does
> Memory]ConnectionOpen
>