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 taking very long time.

I have a table which has near 50million records.
The .dat file created by BCP queryout is of around 51GB.
I am forming the BCP queryout and BCP IN commands in a stored proc and executing the same using xp_cmdshell.
I am using the below BCP options :
/T /b60000000 /a16384 /w /t}@.!{ /r}^#{\n /h"TABLOCK"

With above the QueryOut is taking around 8 hrs and BCP IN another 10 hrs. These figures are not very healthy.

The database recovery mode is set as simple.
Server config: Windows server 2003 X64 edition, with 16 gb RAM.
Any pointers on what could be the reason for this?

The table structure is as below:
CREATE TABLE XXX.dbo.ZZZ (
A nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,B nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,C nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,D datetime
,E numeric(22,7)
,F nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,G nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,H nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,I nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,J nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
,K nvarchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS
,L nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,M nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,N nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,O nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,P nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,Q datetime
,R nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,S nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,T nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,U datetime
,V datetime
,W nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,X nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Y nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Z nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,AA nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,BB nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,CC nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,DD nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,EE Datetime
,FF Datetime
,GG Datetime
,HH Datetime
)

According to the numbers, the data throughput for BCP OUT was ~1.8 MB/sec, for BCP IN - 1.4 MB/sec. Assuming that no blocking is involved the fact that the difference between BCP IN and BCP OUT throughput is only about 20% might be an indication that there is something wrong with the disk IO (or the network IO if you ran BCP from a remote system or if the BCP data files were located on a remote share) since you have plenty of CPU power to provide much higher import/export rates (for a reasonably balanced modern commodity system with gigabit network it can range from 20 to 70 MB/sec). The amount of available memory is not that critical thanks to the amount of data you import/export.

In other words, you need to indentify the bottleneck - the available data strongly suggests that it's the IO, so I would start with inspecting the disk subsystem where you store the BCP data files and/or the network if you store the files on a remote share.

|||

Tenzig,

Thanks for your response !
You are right in guessing that I am running BCP from a remote system. However my data files are stored locally.
However what is intriguing is that the same set up is giving higher throughput for similar process on other tables. So the network can't be blamed as the bottleneck.
I am inspecting the disk subsystem where the data files are stored.
One more thing I observed that the M/c behaves like crazy while running few similar processes simultaneously, with 100% CPU usage and max. PF usage.
What could trigger that?

|||

Did you use the same disk to place the BCP data files for the case where you observed higher throughput when importing/exporting data from other tables? Could you also collect the CPU and IO performance counter logs for the disk where the BCP data files are located for both the "slow" and the "fast" cases? Without the actual numbers it's hard to make any conclusions.

BCP taking very long time.

I have a table which has near 50million records.
The .dat file created by BCP queryout is of around 51GB.
I am forming the BCP queryout and BCP IN commands in a stored proc and executing the same using xp_cmdshell.
I am using the below BCP options :
/T /b60000000 /a16384 /w /t}@.!{ /r}^#{\n /h"TABLOCK"

With above the QueryOut is taking around 8 hrs and BCP IN another 10 hrs. These figures are not very healthy.

The database recovery mode is set as simple.
Server config: Windows server 2003 X64 edition, with 16 gb RAM.
Any pointers on what could be the reason for this?

The table structure is as below:
CREATE TABLE XXX.dbo.ZZZ (
A nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,B nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,C nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,D datetime
,E numeric(22,7)
,F nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,G nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,H nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,I nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,J nvarchar(max) COLLATE SQL_Latin1_General_CP1_CI_AS
,K nvarchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS
,L nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,M nchar(1) COLLATE SQL_Latin1_General_CP1_CI_AS
,N nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,O nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,P nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,Q datetime
,R nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,S nvarchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS
,T nvarchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS
,U datetime
,V datetime
,W nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,X nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Y nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,Z nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,AA nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,BB nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,CC nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,DD nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS
,EE Datetime
,FF Datetime
,GG Datetime
,HH Datetime
)

According to the numbers, the data throughput for BCP OUT was ~1.8 MB/sec, for BCP IN - 1.4 MB/sec. Assuming that no blocking is involved the fact that the difference between BCP IN and BCP OUT throughput is only about 20% might be an indication that there is something wrong with the disk IO (or the network IO if you ran BCP from a remote system or if the BCP data files were located on a remote share) since you have plenty of CPU power to provide much higher import/export rates (for a reasonably balanced modern commodity system with gigabit network it can range from 20 to 70 MB/sec). The amount of available memory is not that critical thanks to the amount of data you import/export.

In other words, you need to indentify the bottleneck - the available data strongly suggests that it's the IO, so I would start with inspecting the disk subsystem where you store the BCP data files and/or the network if you store the files on a remote share.

|||

Tenzig,

Thanks for your response !
You are right in guessing that I am running BCP from a remote system. However my data files are stored locally.
However what is intriguing is that the same set up is giving higher throughput for similar process on other tables. So the network can't be blamed as the bottleneck.
I am inspecting the disk subsystem where the data files are stored.
One more thing I observed that the M/c behaves like crazy while running few similar processes simultaneously, with 100% CPU usage and max. PF usage.
What could trigger that?

|||

Did you use the same disk to place the BCP data files for the case where you observed higher throughput when importing/exporting data from other tables? Could you also collect the CPU and IO performance counter logs for the disk where the BCP data files are located for both the "slow" and the "fast" cases? Without the actual numbers it's hard to make any conclusions.

bcp problem

this command:

bcp MyDatabase..MyTable in Data\2005\MyFile.bcp.dat /f bcp.fmt /S MyServer /U sa

runs fine when run from its original location on a network drive.

When we copy the files referenced down to the C: drive and execute the command (with the path adjusted appropriately) and run the command I get an error:

Unable to open BCP host data-file

both MyFile.bcp.dat and bcp.fmt exist in the locations specified in the new command line. MyTable exists in MyDatabase. Etc. etc.

Why do we get this error? Are there other files secretly needed?

Thanks,

Jon

hi i 'm also facing the same problem could anybody reply if u have the solution...

i have tried bcp out command to a network drive which give the error

sql state = s1000

could not open host data file

please do help me.

jollyguyjolly@.hotmail.com

BCP issue

Dear gurus,
I can't work out with a problem using our dearest statement, i.e BCP.
Well, I'm trying to import a .DAT file to a table from a DOS session and I
obtain the following error:
C:\>bcp ABS.abs_diariopeticiones in c:\20050929.dat -n -c -Ssql2\inst01
-Uusrdts
-Pdts
Where the hell the problem is?
Thanks for any input or advice and regards,On the strenght and bearing on mind that the name of the database is ABS. An
d
ABS is a reserver word in SQL!!!
"Enric" wrote:

> Dear gurus,
> I can't work out with a problem using our dearest statement, i.e BCP.
> Well, I'm trying to import a .DAT file to a table from a DOS session and I
> obtain the following error:
> C:\>bcp ABS.abs_diariopeticiones in c:\20050929.dat -n -c -Ssql2\inst01
> -Uusrdts
> -Pdts
> Where the hell the problem is?
> Thanks for any input or advice and regards,|||"the name of the database is ABS."
Then you are also missing the owner or just leave it away:
[ABS]..abs_diariopeticiones
HTH, jens Suessmeyer.|||cheers
"Jens" wrote:

> "the name of the database is ABS."
> Then you are also missing the owner or just leave it away:
> [ABS]..abs_diariopeticiones
>
> HTH, jens Suessmeyer.
>

bcp issue

Dear all,
I've got a little issue and I can't work out with it. Using BCP in order to
export the contains of a .dat file into a table:
C:\OFI0501>BCP abs..ABS_OF501 IN 20050726.DAT -e enric.txt -n -Sserver -U
us1 -Pdts1
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Se encontró un EOF inesperado en
un archivo de datos BCP
((suddenly error in bcp file))
How do I find out where the eof mark are?
Does anyone ever used or suffered this error?
Regards,Hi,
EOF means End Of File - there's no particular code. Check if data format in
the file is correct
Peter|||Thanks Rogas69. I knew it. Only was I wondering how to solve it of an
automatically way or something like that. Bearing on mind I've got 200 files
to load...
Anyway
"Rogas69" wrote:

> Hi,
> EOF means End Of File - there's no particular code. Check if data format i
n
> the file is correct
> Peter
>
>|||Enric (Enric@.discussions.microsoft.com) writes:
> I've got a little issue and I can't work out with it. Using BCP in order
> to export the contains of a .dat file into a table:
> C:\OFI0501>BCP abs..ABS_OF501 IN 20050726.DAT -e enric.txt -n -Sserver
> -U us1 -Pdts1
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Se encontr un EOF inesperado
> en un archivo de datos BCP
> ((suddenly error in bcp file))
> How do I find out where the eof mark are?
> Does anyone ever used or suffered this error?
Better to ask if there is anyone who have used BCP and never got any error.
It would at least be easier to count the hands.
These extrmely common error means that the data file does not match the
format specification. BCP finds that the file ends in the middle of a
record.
Since you are using native format, this means that the table definition
does not match the table definition. I have no idea what you expected.
Are the files really in native format? Native format means that data appear
in the field as they appear in SQL Server, that is binary.
Since you mentioned that you had 200 files, I would more expect a text
format...
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|||XXXX..life, yep. I've came to the conclusion that the problem is that these
files were extracted from a 'unknown table' and now they want to load again
into another one...
"Erland Sommarskog" wrote:

> Enric (Enric@.discussions.microsoft.com) writes:
> Better to ask if there is anyone who have used BCP and never got any error
.
> It would at least be easier to count the hands.
> These extrmely common error means that the data file does not match the
> format specification. BCP finds that the file ends in the middle of a
> record.
> Since you are using native format, this means that the table definition
> does not match the table definition. I have no idea what you expected.
> Are the files really in native format? Native format means that data appea
r
> in the field as they appear in SQL Server, that is binary.
> Since you mentioned that you had 200 files, I would more expect a text
> format...
>
> --
> 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
>

bcp error

when I use bcp with "-c" option I get the following error:

c:\bcp test_db.dbo.table_1 out c:\test_db_new_table.dat -e c:\test_db_new_table.err -c -U sa -P abc

the result is:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Code page 862 is not supported by SQL Server
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations

but when I execute the same script with "-f" option I do not have any problem
c:\bcp test_db.dbo.table_1 out c:\test_db_new_table.dat -e c:\test_db_new_table.err -U sa -P abc -f c:\bcp.fmt

how can I use the "-c" option when I want to execute BCP from the command line in batch mode
or is it just a problem in the settings of my SQL Server

10x in advanceOriginally posted by ranmel
when I use bcp with "-c" option I get the following error:

c:\bcp test_db.dbo.table_1 out c:\test_db_new_table.dat -e c:\test_db_new_table.err -c -U sa -P abc

the result is:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Code page 862 is not supported by SQL Server
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unable to resolve column level collations

but when I execute the same script with "-f" option I do not have any problem
c:\bcp test_db.dbo.table_1 out c:\test_db_new_table.dat -e c:\test_db_new_table.err -U sa -P abc -f c:\bcp.fmt

how can I use the "-c" option when I want to execute BCP from the command line in batch mode
or is it just a problem in the settings of my SQL Server

10x in advance

using:

bcp master.dbo.syscolumns out c:\t1.dat -ec:\t1.err -c -Utest -P -SIFOODSV

warning: -C difference form -c

bcp command

Hi all,

I am trying to run a .bat file with this bcp command.

BCP "database.dbo.state" OUT "C:\TEMP\state.dat" -SServerName -U"userid" -P"password" -m1 -n -a65536 -E -q

However, it is not producing me a file as I expected.

Is there any other configuration I need to set before it work?

Any help would appreciated.What error messages are being produced? I don't think I've seen BCP fail silently in years. Have you ever done a successful BCP against that SQL Server from the same client maching?

-PatP|||The error is: "Error in attempting to load a pair of translation tables."|||The error is: "Error in attempting to load a pair of translation tables."

Excuse me?

Is it not creating a file, or not creating a file as "expected'

Because if it's the latter and you expect to "see" data, you won't. It's in native format.

You need to lose the -n and use -c|||The error is: "Error in attempting to load a pair of translation tables."That is definitely not an error message generated by BCP. What other programs are you running that might have generated that message?

-PatP

BCP Insert record error (SQL2005-Window 2003)

Hi All,

When we to inset record into the tables through BCP we are getting following errors.

C:\> bcp ACCT.dbo.dtpro out C:\test\dtpro_TA.dat -T-n
C:\>bcp ACCT.dbo.Semiaa out C:\test\Semiaa_TA.dat -T -n

BCP out works fine only BCP in have problems.

Please can you let me know how to resolved this problem.

===========
Exp - 1
===========

C:\> bcp ACCT.dbo.dtpro in C:\test\dtpro_TA.dat -T-n

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

===========
Exp - 2
===========

C:\>bcp ACCT.dbo.Semiaa in C:\test\Semiaa_TA.dat -T -n

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1

============
Exp-3
============

C:\>bcp ACCT.dbo.SemiH in C:\test\SemiH_TA.dat -T -n

Starting copy...SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
SQLState = 42000, NativeError = 7339
Error = [Microsoft][SQL Native Client][SQL Server]OLE DB provider 'STREAM' for l
inked server '(null)' returned invalid data for column '[!BulkInsert].Created'.

BCP copy in failed

Thanks in Advance

There are many reasons this error can happen. It could be, and often is, that the schemas of the databases are incompatible. Post a reply containing the schemas of these databases if you can. That would greatly help in diagnosing the problem.

Jay

|||

C:\>bcp ACCT.dbo.dtpro in C:\test\dtpro_TA.dat -T-n

Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
******************************************

table dtpro

[id] [int] NULL,
[objectid] [int] NULL,
[property] [varchar](64) NULL,
[value] [varchar](255) NULL,
[uvalue] [nvarchar](510) NULL,
[lvalue] [image] NULL,
[version] [int]NULL,

********************************************

|||The error message typically indicates some sort of corruption in the file due to invalid lines, invalid terminators etc. Can you try to create a dummy file on a different location and try? If you are using the file that you BCPed out then do that again but to a different location and try.|||

The schema seems simple enough. It seems odd that you can't import into the same table you exported from, since your table doesn't contain fields that would preclude that.

I would try what Umachandar suggested, and if that doesn't work, try and isolate which records might be causing the problems by using the -F and -L options of bcp to export subsets.

|||

Were you ever able to get this resolved?

Jay Kint