Bulk Insert

Question

CREATE TABLE [dbo].[HB_Contacts](

[HotelCode] [nvarchar](8) NOT NULL,

[Address] [nvarchar](70) NULL,

[PostalCode] [nvarchar](10) NULL,

[City] [nvarchar](50) NULL,

[CountryCode] [nvarchar](3) NULL,

[Email] [nvarchar](100) NULL,

[Web] [nvarchar](100) NULL,

 CONSTRAINT [PK_HB_Contacts] PRIMARY KEY CLUSTERED 

(

[HotelCode] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

use one2confirm_qatesting 

bulk insert dbo.HB_Contacts

from 'C:\Contacts.csv'

WITH 

(

Fieldterminator =' |',

Rowterminator ='|n'

)

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 1 (HotelCode).

Msg 4864, Level 16, State 1, Line 2

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 1 (HotelCode).

Msg 4865, Level 16, State 1, Line 2

Cannot bulk load because the maximum number of errors (10) was exceeded.

Msg 7399, Level 16, State 1, Line 2

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 2

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Answer 1

Hi Winningvision,

Regarding to the error message, which usually occurs when bulk loading data using incompatible data types.
Please refer to the arcticles as below:
BULK INSERT (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188365.aspx
Specifying Field and Row Terminators
http://msdn.microsoft.com/en-us/library/ms191485.aspx

Meanwhile what version SQL Server are you using? If you are using SQL Server 2005, This problem occurs if the following conditions are true:
• You specify a format file when you run the BULK INSERT statement.
• In the format file, you skip importing a table column.
• The data type of the skipped table column is the NTEXT data type, or the data type of the skipped table column is the NVARCHAR data type.
• The size of the skipped table column is more than 4,000 characters

The solution is try to install hotfix for SQL Server 2005 Service Pack 1.
Please click the following article number to obtain SQL Server 2005 Service Pack 1: http://support.microsoft.com/kb/913089.
For hotfix please obtain from here: http://support.microsoft.com/kb/935446

 

 

---

Regards, Amber zhang

Answer 2

Create a staging table to play with where the date field is specified as a varchar and not a date and see if you can bulk insert into that. It is possible that you have some sort of nonprintable character in the field and thus it isn't a date to the bulk insert.

Once it is a table. do a select like this

Select 'XX'+field4+'XX'

If you see a space bewteen the the Xs and the date, you have an unprintable character. If you want to know what the character is you can use the ASCII () function.

Answer 3

Hello,

It is important to know the version 2000,2005,2008,2008 R2 ) of your SQL Server instance . Could you provide it ?

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.