bcp error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

bcp error

Author

Topic

msiax
Starting Member

3 Posts

Posted - 2014-10-24 : 10:33:57

Hello,

I am using 2k8 and it often comes to a need to do big data import for analysis, so I decide bcp should be the best efficient way to go, however, the bcp is really something that you like it and you also hate it, here is my beginner question:

1. I create a test table:
CREATE TABLE [dbo].[EmployeesTest](
[BusinessEntityID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL)

GO

2. Add a line of data

insert EmployeesTest (NationalIDNumber, LoginID) values ('111111', 'aaaaa')

3. Now I export the data to csv
bcp AdventureWorks2008.dbo.EmployeesTest out C:\Data\EmployeesTest.csv –c –t -S -T

4. create test table for back import data

CREATE TABLE [dbo].[EmployeesTestImport](
[BusinessEntityID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL)

GO

5. Now I want to import data back, to a different table EmployeesTestImport, here I stuck on the bcp:

5.1 I create a format file, the reason I use format file is later on I will have much bigger and complex data file for import:
bcp AdventureWorks2008.dbo.EmployeesTestImport format nul -T -c -f C:\Data\EmployeesTest.fmt

5.2 I use the format file in the bcp and it throws me error
bcp AdventureWorks2008.dbo.EmployeesTestImport in C:\Data\EmployeesTest.csv -f C:\Data\EmployeesTest.fmt -S -T

Error:
Starting copy...
SQLState = S1000, 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

What's wrong with this simple procedure?

msiax
Starting Member

3 Posts

Posted - 2014-10-24 : 10:52:33

If I use Bulk Insert to do the same job, I got a little bit more info for the error:

BULK INSERT EmployeesTestImport
FROM 'C:\Data\EmployeesTest.csv'
WITH (
CODEPAGE = 'RAW'
,DATAFILETYPE = 'char'
,FORMATFILE = 'C:\Data\EmployeesTest.fmt'
,TABLOCK
)
;

Error:
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-24 : 13:40:10

Try adding the -r parameter. The files I've bcp'ed typically use CRLF, so I use -r\r\n.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

msiax
Starting Member

3 Posts

Posted - 2014-10-27 : 12:31:16

quote:
Originally posted by tkizer

Try adding the -r parameter. The files I've bcp'ed typically use CRLF, so I use -r\r\n.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

I am not sure if you want me to add -r to the bcp for generating the format file, I did it and the error doesn't go away nor the error message changes

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources