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 keepnulls (-k) option

Hello,

Do you know in what version of bcp the option keepnulls (-k) was introduced?

I use bcp version 12.5.1 on sybase 12.5.

I know that this is not a Sybase forum and that this option might not be available at all in Sybase bcp but your answer might stil help me figure out my problem.

Thank you

I believe it was intruduced in sql 7.0...

BCP Format File Issues

Hi, All,
I'm having problems with BCP and a format file that I created. The current version of BCP is 8.00.382 and the format file looks like this:
8.0
2
1SQL VARCHAR018"||" 1PRONUM
2SQL VARCHAR030"||"2DESCR
When I run BCP it gives me the following error:
Error = [Microsoft][ODBC SQL Server Driver]Attempt to read unknown version of BCP format file
I then tried modifying the version to 8.00.382 and it then gives me the following error:
Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number found in BCP format-file
I'm assuming that this is because it doesn't like the 8.00.338 but I really am not sure. If anyone can help it would be greatly appreciated.
Cheers,
Neil Guyette
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
nguyette@.gemssensors.com wrote:

>Hi, All,
>I'm having problems with BCP and a format file that I created. The current version of BCP is 8.00.382 and the format file looks like this:
>8.0
>2
>1SQL VARCHAR018"||" 1PRONUM
>2SQL VARCHAR030"||"2DESCR
>
The problem is not with the 8.0 version number. Your file should
probably look more like this:
8.0
2
1SQLCHAR018"||" 1PRONUM""
2SQLCHAR030"\r\n"2DESCR"Arabic_CI_AS"
1. If your host file is a text file, use SQLCHAR (no spaces in that) as
the type.
2. Add a collation column for each column (use "" if you don't want to
specify one)
3. || for the column 2 terminator might be correct, but if your data
file has usual line breaks that you don't want to import, you need to
specify that.
The "Using Format Files" topic in Books Online describes the basic
format file layout, and for many good samples, go to
http://groups.google.co.uk/groups?q=...ie+format+file
Steve Kass
Drew University

>When I run BCP it gives me the following error:
>Error = [Microsoft][ODBC SQL Server Driver]Attempt to read unknown version of BCP format file
>I then tried modifying the version to 8.00.382 and it then gives me the following error:
>Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column number found in BCP format-file
>I'm assuming that this is because it doesn't like the 8.00.338 but I really am not sure. If anyone can help it would be greatly appreciated.
>Cheers,
>Neil Guyette
>
>************************************************* *********************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>

BCP fail

Hi,

I'm a newbie to MSQL trying to examine ways in which I can work with a client's data. I'm using the evaluation version of MSQL.

I'm trying to import data from a .dat file created using bcp by the client. The row delimiter is '#'.

I have created a table with the same amount of columns, varchar, 255, nulls allowed. My bcp command is:

bcp dbase_name..table_name in D:\tables\table.dat -n -o D:\tables\error.err -U MyUser -P MyPass -S Myserver

This returns the following error:

SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

As I understand, this indicates that the format of the destination table is not appropriate for import. However, I am unable to see what further parameters I should specify in the destination table. What have I overlooked?

All help appreciated - thanks for your indulgence.

James TonerI don't know if you ommited this by mistake but if you are using a
row terminator of # then shouldn't also be including -r# in your
bcp in command ?

bckhist.cpp - Failed Assertion = 'm_markCVount < m_maxLogMarks'

The SQL Server (Product Version 8.00..., SQL Server 2000) writes the follow
ing errors:
- SQL Server Assertion: File: >bckhist.cpp>, line=1742, Failed Assertion = '
m_MarkCount < m_maxLogMarks'
- Error: 3624, Severity: 20, State:1
- Error: 3449, Severity: 21, State:1, An error has occured that reequires SQ
LServer to shutdown so that recovery can be performed on database ID...
After these errors -> the SQLServer shutdown...
Please Help, thanks!This maybe an issue with the use of begin transaction with Mark Argument
that updates a table with trigger with insert.
Remove the with Mark argument from the being transaction to avoid the
issue.
If you need further information, I would suggest you open a call with
Microsoft support that will be able to assist you further with this issue.
Regards
Agnes Panosian
SQL Server Support

Batch file and use of isql command

Hi,
I'm trying to figure out how to be able to run a batch
file, go to the database, find the current application
version of our product and based on that result it
returns, run a specific script. We have a table that
holds all of the builds/releases of the application that
have been applied against the database. Based on the
information there (the max build/release number), I want
it to execute a specific SQL script that will upgrade them
to the next build/release.
For example, we have a table called DBVersion with one
column in it (AppsVersion). In this table, it has rows
that are as follows:
8.0.100.0
8.0.101.0
8.0.102.0
We have two upgrade paths that we will support. In the
example above, we can upgrade a customer who are in
8.0.100.0 or 8.0.102.0 to the new release number
8.0.103.0. We have two upgrade scripts...one that holds
all db changes from 8.0.100.0 to 8.0.103.0 and one that
holds all db changes from 8.0.102.0 to 8.0.103.0. I want
to create a batch file that finds the version they are on
and run a specific script to upgrade them to the new
release.
Thank you in advance,
DeeHi, this line of SQL maybe helps you
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')
greatings, Cesar
"Dee" wrote:
> Hi,
> I'm trying to figure out how to be able to run a batch
> file, go to the database, find the current application
> version of our product and based on that result it
> returns, run a specific script. We have a table that
> holds all of the builds/releases of the application that
> have been applied against the database. Based on the
> information there (the max build/release number), I want
> it to execute a specific SQL script that will upgrade them
> to the next build/release.
> For example, we have a table called DBVersion with one
> column in it (AppsVersion). In this table, it has rows
> that are as follows:
> 8.0.100.0
> 8.0.101.0
> 8.0.102.0
> We have two upgrade paths that we will support. In the
> example above, we can upgrade a customer who are in
> 8.0.100.0 or 8.0.102.0 to the new release number
> 8.0.103.0. We have two upgrade scripts...one that holds
> all db changes from 8.0.100.0 to 8.0.103.0 and one that
> holds all db changes from 8.0.102.0 to 8.0.103.0. I want
> to create a batch file that finds the version they are on
> and run a specific script to upgrade them to the new
> release.
> Thank you in advance,
> Dee
>|||Hi Cesar,
Thank you for your response. Actually, the version that I
was implying was our own product's application version and
not SQL Server's.
Thanks again,
Dee
>--Original Message--
>Hi, this line of SQL maybe helps you
>SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'),
>SERVERPROPERTY ('edition')
>greatings, Cesar
>"Dee" wrote:
>> Hi,
>> I'm trying to figure out how to be able to run a batch
>> file, go to the database, find the current application
>> version of our product and based on that result it
>> returns, run a specific script. We have a table that
>> holds all of the builds/releases of the application
that
>> have been applied against the database. Based on the
>> information there (the max build/release number), I
want
>> it to execute a specific SQL script that will upgrade
them
>> to the next build/release.
>> For example, we have a table called DBVersion with one
>> column in it (AppsVersion). In this table, it has rows
>> that are as follows:
>> 8.0.100.0
>> 8.0.101.0
>> 8.0.102.0
>> We have two upgrade paths that we will support. In the
>> example above, we can upgrade a customer who are in
>> 8.0.100.0 or 8.0.102.0 to the new release number
>> 8.0.103.0. We have two upgrade scripts...one that
holds
>> all db changes from 8.0.100.0 to 8.0.103.0 and one that
>> holds all db changes from 8.0.102.0 to 8.0.103.0. I
want
>> to create a batch file that finds the version they are
on
>> and run a specific script to upgrade them to the new
>> release.
>> Thank you in advance,
>> Dee
>.
>

Basic, perhaps silly SQLServer 2005 question

I've previously used version 7 and 2000.

I've recently started to learn ASP.net. I've installed
Visual Web Developer 2005 Express and this includes
SQLServer 2005 in the installation. The task manager
and and the list of services definitely show I've got
SQLServer running on my computer.

Here's the problem: how do I actually use it? I can't
find any tools to create and access databases.

I've tried installing the Enterprise Manager from 2000
but this gives me an error message saying I must use the 2005
tools to connect.

So, is there an Enterprise Manager (or similar) for 2005?
Where do I find it?Download Microsoft SQL Server Management Studio Express from
http://www.microsoft.com/downloads/...&DisplayLang=en

Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145465194.532705.252780@.u72g2000cwu.googlegr oups.com...
> Download Microsoft SQL Server Management Studio Express from
> http://www.microsoft.com/downloads/...&DisplayLang=en

Thanks, thats great

Basic VS Advanced Integration Services

Where can I get a good list of what the exact differences are between the Basic and the Advanced version of SQL Server Integration Services are? I have been looking and looking online. But I don't see much of anything written on this issue online. Please help!

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

-Jamie

|||I have seen this link. But that doesn't give me a good comparison between the Basic and the Enterprise editions of SQL Server Integration Services. Thanks
|||

Yes it does (assuming that by "Basic" you mean "Standard". There is no "Basic" edition).

At the bottom of that page you'll see "SQL Server Integration Services Data Flow Integration" and "Text Mining" with a tick in the "Enterprise" column. That's the difference between Enterprise and other editions. There is nothing else. Note that these include Fuzzy operations.

-Jamie