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 Se
rver does
not ex
ist or access denied.
SQLState = 01000, NativeError = 2
Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]Conn
ectionOpen
(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]Co
nnectionOpen
> (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
0use%20of%20a%20netw
ork%20protocol%20without%20changing%20th
e%20Client%20Network%20settings" target="_blank">http://sqldev.net/faq. htm#FAQ:%20H...work%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
>

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
>

BCP Table Named "Function"

Dear all,
Can a table be named as "Function" in SQL 2000?
I have a table using this name. When I tried to BCP it (to extract
rows out), I got error message ".... near Function."
The BCP command I used was:
BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
Pmypwd -c
Thanks in advance.
Regards,
Goh Tiam Tjai
Try brackets around the table name?
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||Thanks, Kevin.
It works with brackets:
BCP mydb.dbo.[function] OUT d:\extract\Function.bcp -Smypc -Usa
-
Pmypwd -c
Regards,
Goh Tiam Tjai
On Jan 9, 9:01 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Try brackets around the table name?
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
|||You can also use the quotename() function
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||I met this kinda problem at a customer's environment while trying to set up
a Merge Replication.
Developers used "Percent" for a user-defined data type and while I was
trying to create the publication it caused lots of errors. It took some time
to find it out. However this kind of mistakes (or whatever you call it) can
take more time to find out.
Avoid using special words for your stuff.
Ekrem nsoy
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai
|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9kt9hv3fuffd@.corp.supernews.com...
> Try brackets around the table name?
>
I think you left out the part about beating the DB designer around the head
for using a reserved name like this. :-)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

BCP Table Named "Function"

Dear all,
Can a table be named as "Function" in SQL 2000?
I have a table using this name. When I tried to BCP it (to extract
rows out), I got error message ".... near Function."
The BCP command I used was:
BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
Pmypwd -c
Thanks in advance.
Regards,
Goh Tiam TjaiTry brackets around the table name'
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||Thanks, Kevin.
It works with brackets:
BCP mydb.dbo.[function] OUT d:\extract\Function.bcp -Smypc -Usa
-
Pmypwd -c
Regards,
Goh Tiam Tjai
On Jan 9, 9:01 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Try brackets around the table name'
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net|||You can also use the quotename() function
--
Sincerely,
John K
Knowledgy Consulting, LLC
knowledgy.org
Atlanta's Business Intelligence and Data Warehouse Experts
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||I met this kinda problem at a customer's environment while trying to set up
a Merge Replication.
Developers used "Percent" for a user-defined data type and while I was
trying to create the publication it caused lots of errors. It took some time
to find it out. However this kind of mistakes (or whatever you call it) can
take more time to find out.
Avoid using special words for your stuff.
--
Ekrem Önsoy
<gohtiamtjai@.gmail.com> wrote in message
news:963eea6a-681b-4f81-b50f-3fa3792a7a77@.q39g2000hsf.googlegroups.com...
> Dear all,
> Can a table be named as "Function" in SQL 2000?
> I have a table using this name. When I tried to BCP it (to extract
> rows out), I got error message ".... near Function."
> The BCP command I used was:
> BCP mydb.dbo.function OUT d:\extract\Function.bcp -Smypc -Usa -
> Pmypwd -c
> Thanks in advance.
>
> Regards,
> Goh Tiam Tjai|||"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o9kt9hv3fuffd@.corp.supernews.com...
> Try brackets around the table name'
>
I think you left out the part about beating the DB designer around the head
for using a reserved name like this. :-)
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.htmlsql

bcp from a named instance

The instalation has SS2K as default instance and SS2K5 as a named instance.
When runing the following where the db is on the SS2K5 instance:
bcp <dbname.dbo.tablename> format nul -T -n -f <filename>.fmt
I get the following:
SQLState=37000,NativeError=4060
Error=[Microsoft][ODBC SQLServer Driver][SQL Server]Cannot open database
requested in login '<dbname>'. Login fail.
When I add the SS2K5 server name I get the same error.
However when I run bcp against a table on the default instance it runs.
Does bcp only look at the default instance'
--
Thanks in advance
GHUse the -S server_name\Instance_name option to connect to a non-default
instance.
Tom
"GH" <vakar@.community.nospam> wrote in message
news:A5B00431-8DE2-4935-9C4F-9BAF7217B02F@.microsoft.com...
> The instalation has SS2K as default instance and SS2K5 as a named
> instance.
> When runing the following where the db is on the SS2K5 instance:
> bcp <dbname.dbo.tablename> format nul -T -n -f <filename>.fmt
> I get the following:
> SQLState=37000,NativeError=4060
> Error=[Microsoft][ODBC SQLServer Driver][SQL Server]Cannot open database
> requested in login '<dbname>'. Login fail.
> When I add the SS2K5 server name I get the same error.
> However when I run bcp against a table on the default instance it runs.
> Does bcp only look at the default instance'
> --
> Thanks in advance
> GH|||GH (vakar@.community.nospam) writes:
> The instalation has SS2K as default instance and SS2K5 as a named
> instance.
> When runing the following where the db is on the SS2K5 instance:
> bcp <dbname.dbo.tablename> format nul -T -n -f <filename>.fmt
> I get the following:
> SQLState=37000,NativeError=4060
> Error=[Microsoft][ODBC SQLServer Driver][SQL Server]Cannot open database
> requested in login '<dbname>'. Login fail.
> When I add the SS2K5 server name I get the same error.
> However when I run bcp against a table on the default instance it runs.
As Tom said, you need to specify the server as MACHINE\Instance or
.\Instance.
If you are generating the BCP command from within SQL Server and
execute it wityh help xp_cmdshell, get the servername from @.@.servername.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for Tom's input.
Hi GH,
I agree with Tom that you can use the "-S" option to specify the
non-default server instance name. e.g.
bcp AdventureWorks.Sales.Currency out Currency.dat -S
remote-server-name\sql2005instance -T
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi GH,
How are you doing on this issue or does the suggestion in our replies help
you a little? If there is still anything we can help, please feel free to
post here.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)