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.
| 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. |
|
|
|
|
|
|
|
|
bcp error