Hi, I'm working on a Access 2003 adp that's linked to a SQL Server 2000 database. I'm trying to use BULK INSERT to import some data from a text file into a table but I'm getting an error message:
Cannot perform bulk insert. Invalid collation name for source column 13 in format file 'C:\Database\DataImport\bcp.fmt'.
I've been following instructions on how to do this from a couple of websites and BOL but I'm not sure what the problem is. Can anyone advise me on what's going wrong?
Here's the details:
The table creation script:
CREATE TABLE [tbltmpDailyComments] (
[DiaryID] [int] IDENTITY (1, 1) NOT NULL ,
[JobID] [int] NOT NULL ,
[AccDate] [datetime] NOT NULL ,
[Comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weather] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TankReading1Start] [int] NULL ,
[TankReading1Finish] [int] NULL ,
[TankReading2Start] [int] NULL ,
[TankReading2Finish] [int] NULL ,
[FuelDrawn] [int] NULL ,
[FuelDelivered] [int] NULL ,
[FuelStorage] [int] NULL ,
[Serviceman] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FuelError] [int] NULL ,
[upsize_ts] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The FMT file:
8.0
13
1 SQLCHAR 0 4 "," 2 JobID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 8 "," 3 AccDate SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 16 "," 4 Comments SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 4 "," 5 Weather SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "," 6 TankReading1Start SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 4 "," 7 TankReading1Finish SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 4 "," 8 TankReading2Start SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "," 9 TankReading2Finish SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 4 "," 10 FuelDrawn SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 4 "," 11 FuelDelivered SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 4 "," 12 FuelStorage SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 50 "," 13 Serviceman SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 4 "\r\n" 14 FuelError SQL_Latin1_General_CP1_CI_AS
And the text File:
"JobID","AccDate","Comments","Weather","TankReading1Start","TankReading1Finish","TankReading2Start","TankReading2Finish","FuelDrawn","FuelDelivered","FuelStorage","Serviceman","FuelError"
113,"21/8/2008","ore","rain",0,0,0,0,2353,0,-2150,,2150
Thanks,
Peter
Cannot perform bulk insert. Invalid collation name for source column 13 in format file 'C:\Database\DataImport\bcp.fmt'.
I've been following instructions on how to do this from a couple of websites and BOL but I'm not sure what the problem is. Can anyone advise me on what's going wrong?
Here's the details:
The table creation script:
CREATE TABLE [tbltmpDailyComments] (
[DiaryID] [int] IDENTITY (1, 1) NOT NULL ,
[JobID] [int] NOT NULL ,
[AccDate] [datetime] NOT NULL ,
[Comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weather] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TankReading1Start] [int] NULL ,
[TankReading1Finish] [int] NULL ,
[TankReading2Start] [int] NULL ,
[TankReading2Finish] [int] NULL ,
[FuelDrawn] [int] NULL ,
[FuelDelivered] [int] NULL ,
[FuelStorage] [int] NULL ,
[Serviceman] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FuelError] [int] NULL ,
[upsize_ts] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The FMT file:
8.0
13
1 SQLCHAR 0 4 "," 2 JobID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 8 "," 3 AccDate SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 16 "," 4 Comments SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 4 "," 5 Weather SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 4 "," 6 TankReading1Start SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 4 "," 7 TankReading1Finish SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 4 "," 8 TankReading2Start SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 4 "," 9 TankReading2Finish SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 4 "," 10 FuelDrawn SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 4 "," 11 FuelDelivered SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 4 "," 12 FuelStorage SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 50 "," 13 Serviceman SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 4 "\r\n" 14 FuelError SQL_Latin1_General_CP1_CI_AS
And the text File:
"JobID","AccDate","Comments","Weather","TankReading1Start","TankReading1Finish","TankReading2Start","TankReading2Finish","FuelDrawn","FuelDelivered","FuelStorage","Serviceman","FuelError"
113,"21/8/2008","ore","rain",0,0,0,0,2353,0,-2150,,2150
Thanks,
Peter