BCP, ignore errors problem

Hi,
I am trying to import data from a Text file into a database Table using SQLserver BCP utility. I am able to do that when I have all new records in my Text file. But I am getting primary key violation error when I am trying to import the record which is already existing in the table. This is correct, but I want my program to ignore these errors and import only those records which are fine.
I tried [-m maxerrors] option, but it is not working. My BCP program is getting interrupted at the first error itself, even if I give [-m100] option.
my command looks something like this,
bcp pub..employee in C:\data.txt -b1 -m100 -c -t, \n -Sdatabase -Uuser -Ppassword

here -b1 is, processing 1 row per batch transaction
-m100 is, ignoring first 100 errors

please help.

thanks
madhuWhy not use BULK INSERT where it accept the CHECK_CONSTRAINTS hint and CHECK_CONSTRAINT clause, respectively, which allows the user to specify whether constraints are checked during a bulk load.

BCP won't import into simple data table...

Hi all...
alright I'm at a loss here...
I'm using BCP to import data into a table in my database...
I keep getting an error of " unexpected Eof' or somthing like that, so
I figure my problem is with the text file i'm importing from.
To see if I was barking up the right tree, I created a new database
and table.
The table (named table1) has one field 'Data1 (nChar(10), null)'
I've created a text file with one column of data:
123
123
123
and I'm using the following command:
Bcp mydatabase..table1 in f:\text.txt -T -Sbiff\sqlExpress
where mydatabase is the name of my new database and biff\sqlexpress is
the name of my server.
This is so Vexxing - any help would be appreciated.
I figured this one out - I wasn't using the -c switch.

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?
I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegr oups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegr oups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David
|||Any recomended reading?
|||?
|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegr oups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Serv.../dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-S...dp/0764584359/
David

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813
Professional SQL Server 2005 Integration Services
http://www.amazon.com/Professional-Server-Integration-Services-Programmer/dp/0764584359/
David

BCP vs SSIS

I have an application running on Unix that stores the information on
flat files. I want to be able to import all the data from some of those
files to SQL Server 2005 in order to create reports using reporting
services. After the initial import I want to be able to update (daily)
the tables on SQL Server with the changes to the flat files. What is
the more appropriate/easy tool for this scenario BCP or SSIS? Where I
can find information about how to create the "differential" import?I recommand to create a package and load the flatfile and use a conditionnal
split to filter this flow for a particular date (or another information like
a sequential ID stored in the flat file)
(but this required that you must read the entire source flat file)
or, maybe, you can start reading at a particular row the flatfile.
in the 2 cases you have to store anywhere the last reading position.
I don't know if you can, but another option is to truncate this flat file
after processing.
in this case every day the file will contain only new rows.
or if you can create 1 file by day you'll have the better way to load the
data in your hand!!!
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159322299.987054.294170@.i42g2000cwa.googlegroups.com...
>I have an application running on Unix that stores the information on
> flat files. I want to be able to import all the data from some of those
> files to SQL Server 2005 in order to create reports using reporting
> services. After the initial import I want to be able to update (daily)
> the tables on SQL Server with the changes to the flat files. What is
> the more appropriate/easy tool for this scenario BCP or SSIS? Where I
> can find information about how to create the "differential" import?
>
BCP can only do the load part. SSIS can do the end-to-end process. SSIS can
FTP the files down from your Unix box, Load them into staging tables, and
run SQL to merge them into your production tables.
SSIS can also do more complicated data flows if you want to do perform the
merge and any data cleansing logic using an SSIS Data Flow task instead of
SQL Queries.
David|||Any recomended reading?|||"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1159416107.959931.263660@.m73g2000cwd.googlegroups.com...
> ?
>
Integration Services on MSDN
http://msdn2.microsoft.com/en-us/sql/aa336312.aspx
Microsoft SQL Server 2005 Integration Services
http://www.amazon.com/Microsoft-Ser...5
84359/
David

BCP utility replaces German with junk characters

Hi All,
I am using command line bcp utility of SQL Server to import data from a text
file to database.

I have some german words in the text file and after
import the German characters are lost.

see eg below.

Input : Khner, Klaus -> Text file value
OutPut: Khner, Klaus -> Table data, after import.

I am using unicode in the commad line.
The collation setting is LATIN

can anyone show some light.

Thanks in advance

Manoj.Manoj (mcmanoj_2000@.yahoo.com) writes:
> I am using command line bcp utility of SQL Server to import data from a
> text file to database.
> I have some german words in the text file and after
> import the German characters are lost.
> see eg below.
> Input : Khner, Klaus -> Text file value
> OutPut: K?hner, Klaus -> Table data, after import.
> I am using unicode in the commad line.
> The collation setting is LATIN

Since BCP is a command-line utility it's defaul code page is the OEM
code page. Therefore, if your server uses an ANSI page, and this is
the normal, there is an automatic conversion from OEM to ANSI. Problem
is if the file is in ANSI already - which it often is.

BCP offers the -C option to control this. The simplest is to use
-C RAW to turn off conversion.

See the description of BCP in Books Online for more details.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP Utility - Skip rows..

Hello All,

Does the BCP utility enable you to selectively import rows from a flat
file to a table ?

For example:

The first column in my flat file contains a record type - 1, 2..7
I only need to import types 1, 2, & 3

Can this be specified in the .fmt file ?

Thanks in advance
hharryhharry (paulquigley@.nyc.com) writes:
> Does the BCP utility enable you to selectively import rows from a flat
> file to a table ?
> For example:
> The first column in my flat file contains a record type - 1, 2..7
> I only need to import types 1, 2, & 3
> Can this be specified in the .fmt file ?

It depends on the format of the records, but I would say that it is
highly unlikely. In fact, if the record types are heterogeneous, you may
not be able to write a format file to describe the file at all.

If they record types are homegeneous to fit into one format, you can bulk
load into a staging table, and the move on to the target table from there
with the interesting rows.

Also, if the records appears evenly in strict order: 12345671234567...
you can handle all as one big record. But I would not really expect
your file be like that...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There are 7 different record types within the file - all different
lengths.
I am going to parse the file via a dotnet exe.
Thanks for the assistance

Erland Sommarskog wrote:
> hharry (paulquigley@.nyc.com) writes:
> > Does the BCP utility enable you to selectively import rows from a
flat
> > file to a table ?
> > For example:
> > The first column in my flat file contains a record type - 1, 2..7
> > I only need to import types 1, 2, & 3
> > Can this be specified in the .fmt file ?
> It depends on the format of the records, but I would say that it is
> highly unlikely. In fact, if the record types are heterogeneous, you
may
> not be able to write a format file to describe the file at all.
> If they record types are homegeneous to fit into one format, you can
bulk
> load into a staging table, and the move on to the target table from
there
> with the interesting rows.
> Also, if the records appears evenly in strict order:
12345671234567...
> you can handle all as one big record. But I would not really expect
> your file be like that...
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||One of our people created a table that was, essentially:

record_type int
rest_of_record varchar(long_enough)

loaded the table with a bcp format that was just a field for the record type
and a varchar field for the rest.

Then, he created a SP that used a cursor to walk through the table and T-SQL
decoded the records into various fields and did the inserts into destination
tables.

I found out what they'd done after we upgraded from SQL Server 6.5 and the
process stopped working because the rows no longer came out in
First-In/First-Out order. The SP was almost entirely unmaintainable,
anyway.

We wrote something maintainable and understandable in VB6 (with record
types, liberal use of constant declarations) in a few hours. Much better. *

I wonder if you could use DTS to accomplish what you want? If you haven't
already, why not drop the question into microsoft.public.sqlserver.dts?

* If I remember correctly, the emergency workaround was to add an identity
column to the work table and have the cursor pull rows in sorted order on
the identity column.

"hharry" <paulquigley@.nyc.com> wrote in message
news:1109366729.311250.16040@.l41g2000cwc.googlegro ups.com...
> Hello All,
> Does the BCP utility enable you to selectively import rows from a flat
> file to a table ?
> For example:
> The first column in my flat file contains a record type - 1, 2..7
> I only need to import types 1, 2, & 3
> Can this be specified in the .fmt file ?
>
> Thanks in advance
> hharry|||dh (dh@.news.net) writes:
> One of our people created a table that was, essentially:
> record_type int
> rest_of_record varchar(long_enough)
> loaded the table with a bcp format that was just a field for the record
> type and a varchar field for the rest.
> Then, he created a SP that used a cursor to walk through the table and
> T-SQL decoded the records into various fields and did the inserts into
> destination tables.

Yeah, that's a normal approach to do it. You write a program that reads
the file. But T-SQL is a poor choice for the task, since it's not good
on string handling. And while you can add an IDENTITY, you cannot be
really sure that it works anyway.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP Utility

Hi all,

I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:

AppointmentKey
SurgerySlotKey
PatientKey
Cancelled
Continuation
Deleted
TimeArrived

I would like to import only two of these fields into a table called tbl_Appointments e.g.

AppointmentKey
TimeArrived

I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible?

ThanksReplace IN with FORMAT and add "-f file_name.fmt" without quotes on the command line when issuing BCP statement.

Open your favorite text editor and modify the format file to look something like this:8.0
7
1 SQLINT 0 0 "\t" 1 AppointmentKey ""
2 SQLCHAR 0 255 "\t" 0 SurgerySlotKey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 255 "\t" 0 PatientKey SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 255 "\t" 0 Cancelled SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 255 "\t" 0 Continuation SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 255 "\t" 0 Deleted SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 255 "\r\n" 7 TimeArrived SQL_Latin1_General_CP1_CI_AS|||Thanks for replying and you've certainly pointed me in the right direction. I've almost got it working but for a couple of problems. The first problem is that when it imports the Appointment key it seems to be adding a tab character or something to before the key e.g. 341084.80096.Appointment becomes 341084.80096.Appointment when imported to the new table. Also, I can't get it to import the date field 'TimeArrived' (please see attached), SQL server throws up the following error message:

Server: Msg 4829, Level 16, State 1, Line 1
Could not bulk insert. Error reading destination table column name for source column 8 in format file

Even although the column name is correct...I have tried importing the other date fileds and it does the same thing.

I have attached my .fmt file for you to have a look at.

Thanks|||The second token in the format file (where you have SQLCHAR etc) refers to the format of the field IN THE HOST FILE, not on the server. If this a date in ASCII readable string format, e.g. "Jan 1 2004 10:00AM" or "2004-01-01" the this should be set to SQLCHAR

see Books online : bcp utility / format files / Using format files|||Tried setting the date field to SQLCHAR (see attached file) but it's still giving me the same error message:

Server: Msg 4829, Level 16, State 1, Line 1
Could not bulk insert. Error reading destination table column name for source column 8 in format file '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt'

Here's the SQL I'm using:

BULK INSERT tbl_UpdateTimeArrived FROM '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\xv_Appointment.dat'
WITH (FORMATFILE = '\\Henke\Corporate\48hrAccess\48hrDataArchive\8009 6\bcp.fmt

The only other thing that may affect it is the .dat file in question is from a SQL Server 6.5 database and I'm importing it into a SQL 2000 database.|||maybe your SQLBIT columns should be SQLCHAR as well...|||Still didn't work but I'm slowly getting to the bottom of it. Tried importing a non-date, non-bit field and it imported fine (again see attached) - so it seems to be the date fields that are causing the problem...Any ideas?|||can you attach say top 5 lines of your data file ?|||I've figured it out. I need the same number of fields in the destination table as the table in the .dat file. Even although I only want to import two fileds. In effect, the destination table needs to be an exact replica regardless of whether I want to use all the fields or not!!

If anybodyt knows any different then please let me know.

BCP Transaction Does not roll back

Hi All,
I'm trying to import some data using the BCP command line utility.
I've set the maxerrors switch to 0. In case of an exception such as a
cast exception the import fails leaving the table state dirtied.
ie...with partial data imported.
From what I understand from a few other posts the transaction logs
only store the space alocated and not the actual data.
How does one ensure that the import is done in a transaction ?
Any suggestions/ideas will be great.
Regards,
Avinash
Can you post the command you are running
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Avinash" <avinashraj@.gmail.com> wrote in message
news:f22f61b0.0412050250.3d6ef381@.posting.google.c om...
> Hi All,
> I'm trying to import some data using the BCP command line utility.
> I've set the maxerrors switch to 0. In case of an exception such as a
> cast exception the import fails leaving the table state dirtied.
> ie...with partial data imported.
> From what I understand from a few other posts the transaction logs
> only store the space alocated and not the actual data.
> How does one ensure that the import is done in a transaction ?
> Any suggestions/ideas will be great.
> Regards,
> Avinash

bcp to import data into a partitioned view

I created a partitioned view as
CREATE VIEW empMaster AS
SELECT * FROM emp1
UNION ALL
SELECT * FROM emp2
(where emp1 and emp2 have check constraints on their primary key)
I tried to run bcp to import data into this view.
i get an error message :"Partitioned view 'empMaster' is not updatable as
the target of a bulk operation."
(iam able to do a simple INSERT into this view though)
Does this mean that a bulk operation to a partitioned view is not possible ?
thanks,
Vivek T S
Member Technical Staff (Inucom)
Vivek,
I believe that is correct. You will have to split your bulk import data and
send it to the correct tables. You also should read the following article:
http://support.microsoft.com/default...b;en-us;286134
Russell Fields
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:5FF78194-3CC7-4BA5-8886-3F036A9C99C4@.microsoft.com...
> I created a partitioned view as
> CREATE VIEW empMaster AS
> SELECT * FROM emp1
> UNION ALL
> SELECT * FROM emp2
> (where emp1 and emp2 have check constraints on their primary key)
> I tried to run bcp to import data into this view.
> i get an error message :"Partitioned view 'empMaster' is not updatable as
> the target of a bulk operation."
> (iam able to do a simple INSERT into this view though)
> Does this mean that a bulk operation to a partitioned view is not possible
?
> thanks,
> --
> Vivek T S
> Member Technical Staff (Inucom)

bcp to import data into a partitioned view

I created a partitioned view as
CREATE VIEW empMaster AS
SELECT * FROM emp1
UNION ALL
SELECT * FROM emp2
(where emp1 and emp2 have check constraints on their primary key)
I tried to run bcp to import data into this view.
i get an error message :"Partitioned view 'empMaster' is not updatable as
the target of a bulk operation."
(iam able to do a simple INSERT into this view though)
Does this mean that a bulk operation to a partitioned view is not possible ?
thanks,
--
Vivek T S
Member Technical Staff (Inucom)Vivek,
I believe that is correct. You will have to split your bulk import data and
send it to the correct tables. You also should read the following article:
http://support.microsoft.com/defaul...kb;en-us;286134
Russell Fields
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:5FF78194-3CC7-4BA5-8886-3F036A9C99C4@.microsoft.com...
> I created a partitioned view as
> CREATE VIEW empMaster AS
> SELECT * FROM emp1
> UNION ALL
> SELECT * FROM emp2
> (where emp1 and emp2 have check constraints on their primary key)
> I tried to run bcp to import data into this view.
> i get an error message :"Partitioned view 'empMaster' is not updatable as
> the target of a bulk operation."
> (iam able to do a simple INSERT into this view though)
> Does this mean that a bulk operation to a partitioned view is not possible
?
> thanks,
> --
> Vivek T S
> Member Technical Staff (Inucom)sql

BCP support in SQL Server 2005 - Doubts

Hi friends,

I am currently using SQL Server 2000 server. I have run some BCP command to import data to SQL from CSV and Text files with the help of FORMAT files through Windows Scheduler.

My doubt is, if I swich to SQL Server 2005, whether the same BCP commands will supported? If no, What are the things I need to do to run the BCP commands in 2005 as in 2000?

I heard that the BCP utility is no longer support in 2005 and this can be done thru SSIS utility. Is it right...?

Is SQL Server 2005 support to use the BCP commands without going to SSIS utility?

Please help. Thanks in advance.

Regards,

Sethu.

BCP should still work for you -it is a backwards compatibility issue.

The SQL 2005 utility is SQLCommand.exe. It has almost the identical command set as BCP. Refer to Books Online, Topic: 'SQLCommand utility'

bcp resource file error

I am trying to run a bcp import and I am getting the following error:
Unable to Load BCP resource dll. BCP cannot continue.
Does anyone have any idea what might cause this error?
<d> schrieb im Newsbeitrag news:eWq8%23a82EHA.3120@.TK2MSFTNGP12.phx.gbl...
> I am trying to run a bcp import and I am getting the following error:
> Unable to Load BCP resource dll. BCP cannot continue.
> Does anyone have any idea what might cause this error?
You might have to change your PATH appropriately.
robert

BCP questions

I'm using BCP for the first time and have a problem.
I'm trying to import some client data into SQL Server. The data is in
CSV format. The problem is that the data contains embedded commas, so
BCP cannot correctly parse the commas.
So, my question is:
I need to get the data re-exported, but in what format?
Can BCP handle field delimiters, such as surrounding the data in
quotes?
Should I have them use something other than commas to separate fields?
Or should I ask them to use a fixed-length format?
I'm new to BCP, so I don't know what BCP is capable of doing, or how
easy/hard each way would be.blindsey,
> I'm using BCP for the first time and have a problem.
> I'm trying to import some client data into SQL Server. The data
> is in CSV format. The problem is that the data contains embedded
> commas, so BCP cannot correctly parse the commas.
> So, my question is:
> I need to get the data re-exported, but in what format?
> Can BCP handle field delimiters, such as surrounding the data in
> quotes?
Yes, it can. But you will need a bcp format file to handle the
quotes. Format files are documented in Books Online.
> Should I have them use something other than commas to separate
> fields?
Yes, if you have any say in the matter, by all means have the data
file created in a format that is convenient for you to load.
Tabs are the default field delimiter for bcp, but you can also
specify any other character that does not appear in the data you are
loading. | or # are popular delimiters. You can also use a
deliniting character string of your own choosing, such as [#|], if
you cannot find a single character that does not appear in the data.
> Or should I ask them to use a fixed-length format?
Fixed-length fields require a bcp format file, making them not quite
as convenient to load as delimited files.
> I'm new to BCP, so I don't know what BCP is capable of doing, or
> how easy/hard each way would be.
Bcp is a powerful tool to efficiently load and transform large
amounts of data. I you need to load data from various on a regular
basis, it is worth the time to learn about bcp formt files.
Lindasql

bcp Question

Without using format file, is there a way for bcp to ignore single quote in
an import file? Thanks.Why are you not wanting to use a format file?
mason wrote:
> Without using format file, is there a way for bcp to ignore single quote i
n
> an import file? Thanks.
>|||Try -q option. More information in BOL.|||I have no problem using format files, but it would be simpler to do without
in terms of maintenance. -c option works well when there is no quote in
import files (about 36 of them). I just want to verify whether there is an
option somewhere to ignore quotes for bcp.
"Carl Imthurn" <nospam@.all.com> wrote in message
news:OvDmLQsQGHA.5768@.tk2msftngp13.phx.gbl...
> Why are you not wanting to use a format file?
> mason wrote:|||Just tried. It's not what I wanted. I think -q affects identifiers, not the
data elements in a data file.
For example, a data record looks like this. I would like bcp to ignore
single quotes when importing.
1,'John','2006-03-08 12:00:00.000'
"Green" <subhash.daga@.gmail.com> wrote in message
news:1141831907.411815.10740@.p10g2000cwp.googlegroups.com...
> Try -q option. More information in BOL.|||Would it be possible to create the data file without the apostrophes?
Looking at your example, it seems that you have a comma-delimited file
with apostrophes delimiting text fields but not numeric fields.
I needed to deal with that situation once - I was faced with quotes
rather than apostrophes, but the concept was the same. The only thing I
was able to figure out was to import the text file to a SQL Server table
via DTS because of the fact that some fields have a delimiting
character, some do not.
Good luck - hope this was helpful.
Carl
mason wrote:

> Just tried. It's not what I wanted. I think -q affects identifiers, not
> the data elements in a data file.
> For example, a data record looks like this. I would like bcp to ignore
> single quotes when importing.
> 1,'John','2006-03-08 12:00:00.000'
>
>
> "Green" <subhash.daga@.gmail.com> wrote in message
> news:1141831907.411815.10740@.p10g2000cwp.googlegroups.com...
>
>|||Of course. I will most likely do that at the export end. Since the files may
also feed Sybase and Oracle, it gets ugly. Thanks.
"Carl Imthurn" <nospam@.all.com> wrote in message
news:eJ2605sQGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Would it be possible to create the data file without the apostrophes?
> Looking at your example, it seems that you have a comma-delimited file
> with apostrophes delimiting text fields but not numeric fields.
> I needed to deal with that situation once - I was faced with quotes rather
> than apostrophes, but the concept was the same. The only thing I was able
> to figure out was to import the text file to a SQL Server table via DTS
> because of the fact that some fields have a delimiting character, some do
> not.
> Good luck - hope this was helpful.
> Carl
> mason wrote:
>
>

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
Dylan
Have you checked the event log to verify you are not getting Disk I/O
errors?
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BIN
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BIN
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BIN
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BIN
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BIN
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>
|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
(Created
> by
> certain
> use
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
>
|||hi Dylan,
I to am getting the same error of bcp you getting. Did you find the cause...if so please let me know.
Thanks
Arzan
"Dylan Kruger" wrote:

> I have not as i thought it would be unlikely that it owuld be that , but i
> will check it anyways ,
> Thanks
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> --
> --
> (Created
>
>
sql

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading B
CP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
DylanHave you checked the event log to verify you are not getting Disk I/O
errors?
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading
BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BINeen">
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BINeen">
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BINeen">
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BINeen">
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BINeen">
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
(Created[vbcol=seagreen]
> by
> certain
> use
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
> SQL_Latin1_General_Cp437_BIN
>|||hi Dylan,
I to am getting the same error of bcp you getting. Did you find the cause...
if so please let me know.
Thanks
Arzan
"Dylan Kruger" wrote:

> I have not as i thought it would be unlikely that it owuld be that , but i
> will check it anyways ,
> Thanks
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> --
> --
> (Created
>
>

BCP problem on different servers

I=B4m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebasti=E1n1) can you bcp manually from a command prompt?
2) can you connect using sqlcmd and/or SSMS?
Likely issue is that appropriate network/connectivity settings are not there
for SQL2K5.
TheSQLGuru
President
Indicium Resources, Inc.
<spiccolotto@.gmail.com> wrote in message
news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
Im using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebastin|||Yes, I can bcp manually and connect using SSMS.
I will explain more my scenario:
Server A have SQL Server 2000 / Windows 2000.
Here, I have a VB6 DLL and CSV file. Inside this dll I execute BCP
command to send csv file data to Remote Database (On Server B).
BCP command executed from VB6 DLL doesn=B4t work. If I execute same BCP
command but now from command line (cmd) all works ok.
Server B have SQL Server 2005 / Windows 2003.
My questions are:
=BFAny incompatibility between BCP Versions (80 and 90)?
=BFWhy bcp works from command line (cmd)?
=BFI need to modify any permission?
Regards
Sebasti=E1n.
On Apr 18, 4:29 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) can youbcpmanually from a command prompt?
> 2) can you connect using sqlcmd and/or SSMS?
> Likely issue is that appropriate network/connectivity settings are not th=
ere
> for SQL2K5.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <spiccolo...@.gmail.com> wrote in message
> news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
> I=B4m usingBCPcommand inside VB6 DLL to import data from csv file to
> database.
> Normally it works fine, but when my database is installed in a server
> with Windows 2003 and SQL Server 2005 I had problems...bcpnot
> works... In this scenario, If I execute the samebcpcommand but now
> from command line all is ok... what is theproblem?
> Thanks
> Sebasti=E1nsql

BCP problem on different servers

I=B4m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebasti=E1n1) can you bcp manually from a command prompt?
2) can you connect using sqlcmd and/or SSMS?
Likely issue is that appropriate network/connectivity settings are not there
for SQL2K5.
--
TheSQLGuru
President
Indicium Resources, Inc.
<spiccolotto@.gmail.com> wrote in message
news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
I´m using BCP command inside VB6 DLL to import data from csv file to
database.
Normally it works fine, but when my database is installed in a server
with Windows 2003 and SQL Server 2005 I had problems... bcp not
works... In this scenario, If I execute the same bcp command but now
from command line all is ok... what is the problem ?
Thanks
Sebastián|||Yes, I can bcp manually and connect using SSMS.
I will explain more my scenario:
Server A have SQL Server 2000 / Windows 2000.
Here, I have a VB6 DLL and CSV file. Inside this dll I execute BCP
command to send csv file data to Remote Database (On Server B).
BCP command executed from VB6 DLL doesn=B4t work. If I execute same BCP
command but now from command line (cmd) all works ok.
Server B have SQL Server 2005 / Windows 2003.
My questions are:
=BFAny incompatibility between BCP Versions (80 and 90)?
=BFWhy bcp works from command line (cmd)?
=BFI need to modify any permission?
Regards
Sebasti=E1n.
On Apr 18, 4:29 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) can youbcpmanually from a command prompt?
> 2) can you connect using sqlcmd and/or SSMS?
> Likely issue is that appropriate network/connectivity settings are not th=ere
> for SQL2K5.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <spiccolo...@.gmail.com> wrote in message
> news:1176907053.100891.181710@.b58g2000hsg.googlegroups.com...
> I=B4m usingBCPcommand inside VB6 DLL to import data from csv file to
> database.
> Normally it works fine, but when my database is installed in a server
> with Windows 2003 and SQL Server 2005 I had problems...bcpnot
> works... In this scenario, If I execute the samebcpcommand but now
> from command line all is ok... what is theproblem?
> Thanks
> Sebasti=E1n

bcp problem

I am trying to import data from a file. The file has entries like
1437"PJ_001"3"PJ/001 "
5746"PJ_002"3"PJ/002 "
while the table is defined as
kint4nulls
ftext16nulls
cint4nulls
pchar30nulls
When I use command
bcp "MyDatabase.dbo.MyTable" in "D:\Development\MyDatabase\MyFile.txt" -c
-S"MyPc\MyInstance" -U"sa" -P"MyPass"
I get error message
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
on every entry. Can anybody explain why, or even better, how to get this
working?
Thanks in advance!
Hans.
Have you looked at using a format file for your BCP command? BOL will
explain how to use them.
"Hans" <Hans@.discussions.microsoft.com> wrote in message
news:F46030CF-34BD-45F9-BF49-9E8868A3207F@.microsoft.com...
> I am trying to import data from a file. The file has entries like
> 1437 "PJ_001" 3 "PJ/001 "
> 5746 "PJ_002" 3 "PJ/002 "
> while the table is defined as
> k int 4 nulls
> f text 16 nulls
> c int 4 nulls
> p char 30 nulls
> When I use command
> bcp "MyDatabase.dbo.MyTable" in "D:\Development\MyDatabase\MyFile.txt" -c
> -S"MyPc\MyInstance" -U"sa" -P"MyPass"
> I get error message
> SQLState = 22001, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
> on every entry. Can anybody explain why, or even better, how to get this
> working?
> Thanks in advance!
> Hans.