January 14, 2010 at 9:53 pm
Hi
I run bcp to export data:
bcp out with parameters -E -k -N
bcp in with the same parameters into an empty table that is created just before bcp in. Usually to create the destination table just create script from source table is used without any indexes or table constraints.
This process is used for different tables, on different databases.
Recently the person who created a destination table removed NOT NULL from columns, making all columns nullable. As a result bcp in failed with the error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
When the create script was corrected to have correct null column constraints, bcp in was executed successfully. But I was asked, what was the reason of the error and why bcp behaved like that.
I do not know the answer, tried to google and could not find the explanation. I understand that when bcp is used without format file, then the destination table must have exactly the same structure as the source table as bcp data files are just like comma separated files. I understand that the type, length, order of columns must be the same. I understand that when I keep null values (-k in bcp call) and the destination table doesn't allow nulls, it will fail. But why if null values are allowed on all columns in the destination table, but in the source nulls are allowed just in some columns, it fails? So let's say I have a source table
create table t1_source(
[f1] [int] NOT NULL,
[f2] [int] NOT NULL,
[f3] [datetime] NOT NULL,
[f4] [int] NOT NULL,
[f5] [int] NULL,
[f6] [varchar](136) NULL,
[f10] [timestamp] NULL
)
insert into t1_source(f1, f2, f3, f4, f5, f6) values(1, 10, '20091010', 100, 1000, '1123')
insert into t1_source(f1, f2, f3, f4, f5, f6) values(2, 10, '20091011', 101, 1000, null)
insert into t1_source(f1, f2, f3, f4, f5, f6) values(3, 10, '20091012', 102, 1000, '1123')
insert into t1_source(f1, f2, f3, f4, f5, f6) values(4, 10, '20091013', 103, 1000, null)
insert into t1_source(f1, f2, f3, f4, f5, f6) values(5, 10, '20091014', 104, 1000, '1123')
bcp <dbName>.dbo.t1_source out "C:\Temp\t1.dat" -E -k -N -e "C:\temp\t1.err" -T
why bcp fails if the destination table looks like:
create table t1_dest(
[f1] [int] NULL,
[f2] [int] NULL,
[f3] [datetime] NULL,
[f4] [int] NULL,
[f5] [int] NULL,
[f6] [varchar](136) NULL,
[f10] [timestamp] NULL
)
and doesn't fail if it looks like
create table t1_dest(
[f1] [int] NOT NULL,
[f2] [int] NOT NULL,
[f3] [datetime] NOT NULL,
[f4] [int] NOT NULL,
[f5] [int] NULL,
[f6] [varchar](136) NULL,
[f10] [timestamp] NULL
)
?
I use
bcp <dbName>.dbo.t1_dest in "C:\Temp\t1.dat" -E -k -N -e "C:\temp\t1.err" -T
Thank you for your help.
January 18, 2010 at 5:00 am
Can you attach a sample of your data file?
Also you can try to use a format file and skip the null columns.
January 18, 2010 at 6:46 pm
I've attached t1.txt which is bcp data file. Rename to .dat to run the bcp command that is in my above post.
BCP works fine if NULL/NOT NULL is set for the destination table the same way as source table (there are 2 different create statements for the destination table in my above post). I just do not understand why bcp needs that.
About format file: can format file be created automatically without user interaction? I call BCP from application for a set of tables (not less than 10) with a lot of columns so it is impossible for me to ask the customer to press enter for each field when bcp creates format file.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply