Bulk Insert: Unexpected end-of-file (EOF) encounte

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk Insert: Unexpected end-of-file (EOF) encounte

Author

Topic

viatorg
Starting Member

3 Posts

Posted - 2007-06-01 : 16:50:57

Hi all,

Trying to bulk insert a csv file from a bcp command generated file but, get
this error:

Server: Msg 4839, Level 16, State 1, Line 3
Cannot perform bulk insert. Invalid collation name for source column 45 in
format file '\\MyServer\drive_e\temp\usage.fmt'.

First Column in Distination table is identity column and the table already
contains many rows. See below for DDL.

Bulk Insert Statment:

BULK INSERT Mytable FROM '\\MyServer\drive_e\temp\usage.csv'
WITH (DATAFILETYPE = 'Native',
TABLOCK,
BATCHSIZE = 100,
FORMATFILE = '\\MyServer\drive_e\temp\usage.fmt',
ROWTERMINATOR = '\n'

This is the format file:

8.0
45
1 SQLINT 0 12 "/t" 2 UsageID SQL_Latin1_General_CP1_CI_AS
.......
.......
43 SQLCHAR 1 10 "/t" 44 api_BillDate SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 1 50 "/t" 45 api_FileName SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 1 10 "\r\n" 46 AcctCode_new SQL_Latin1_General_CP1_CI_AS

this is the distination table:

CREATE TABLE [Mytable] (
[UsageRowID]int IDENTITY(1,1) NOT NULL ,
[UsageID]int NOT NULL,
........
........
[api_BillDate]char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[api_FileName]char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AcctCode_new]varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

This is the bcp command that created the csv file:

DECLARE @bcpCommand VARCHAR(2000)
SET @bcpCommand = 'bcp "SELECT ..,...,..,.,...,... from Sometable" queryout
E:\temp\usage.csv -SMyServer -T -n -b 100'

EXECUTE xp_cmdshell @bcpCommand
go

thanks
gv

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-03 : 10:04:19

The line terminator is in the format file so you don't need it in th ebulk insert command.
You also seem to be defining a fixed width tab delimited files which is a bit odd.
do you have a crlf after the last line in the format file?

Have a look at
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources