Type Mismatch Error On Bulk Insert

[Mike]

I am getting a type mismatch error when I do a bulk insert.

---Begin Error Msg---
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 14
(STDCOST).
---End Error Msg---

The STDCOST is set to decimal (28,14) and is a formatted in Access as a
number, single with 14 decimal. I don't know why I would be getting a Type
Mismatch error.

Any idea?

Mike

[Hugo Kornelis]

On Thu, 13 Oct 2005 20:56:28 GMT, Mike wrote:

I am getting a type mismatch error when I do a bulk insert.

---Begin Error Msg---
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 14
(STDCOST).
---End Error Msg---

The STDCOST is set to decimal (28,14) and is a formatted in Access as a
number, single with 14 decimal. I don't know why I would be getting a Type
Mismatch error.

Any idea?

Mike

Hi Mike,

I just replied to your previous (identical) message. Please don't
double-post!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Mike (no***@forgetit.com) writes:

I am getting a type mismatch error when I do a bulk insert.

---Begin Error Msg---
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 14
(STDCOST).
---End Error Msg---

The STDCOST is set to decimal (28,14) and is a formatted in Access as a
number, single with 14 decimal. I don't know why I would be getting a Type
Mismatch error.

Any idea?

No. Telepathy is not my strong side. Please post:

1) CREATE TABLE for the table you are importing into.
2) The BULK INSERT command.
3) A sample data file (please zip into an attachment, to avoid transport
damages).
4) Any format file you are using.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Mike]

Ok This is what I have figured out so far.

1) I was missing a column on the import file. I added the last column.

2) Now I get the following error:

---Begin Error Msg---
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 47, column 3
(PRICE).
---End Error Msg---

This error is repeated for 10 different row from Row 47 to 379 and then the
import stops because the max error level has been reached.

The columns are configured as follows:

SQL Table-
Name: PRICE
Data Type: decimal (28,14)

MS-Access Table
Name: PRICE
Data Type: numeric (single w/auto decimal)

The data is export to a CSV text file.

I have triple checked the column for junk data and I can not find any. The
data ranges from 0 through 10000.

I am really stuck on this now. I don't know what to do.

Can anyone help me?

Mike Charney

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

Mike (no***@forgetit.com) writes:

I am getting a type mismatch error when I do a bulk insert.

---Begin Error Msg---
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 14
(STDCOST).
---End Error Msg---

The STDCOST is set to decimal (28,14) and is a formatted in Access as a
number, single with 14 decimal. I don't know why I would be getting a
Type
Mismatch error.

Any idea?

No. Telepathy is not my strong side. Please post:

1) CREATE TABLE for the table you are importing into.
2) The BULK INSERT command.
3) A sample data file (please zip into an attachment, to avoid transport
damages).
4) Any format file you are using.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Mike (no***@forgetit.com) writes:

2) Now I get the following error:

---Begin Error Msg---
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 47, column 3
(PRICE).
---End Error Msg---

This error is repeated for 10 different row from Row 47 to 379 and then
the import stops because the max error level has been reached.

The columns are configured as follows:

SQL Table-
Name: PRICE
Data Type: decimal (28,14)

MS-Access Table
Name: PRICE
Data Type: numeric (single w/auto decimal)

The data is export to a CSV text file.

I will have to repeat what I said in my previous post:

1) CREATE TABLE for the table you are importing into.
2) The BULK INSERT command.
3) A sample data file (please zip into an attachment, to avoid transport
damages).
4) Any format file you are using.

Mind you, I don't know your Access table - in fact I don't know Access
at all.

It's probably a good idea to include the first 50 lines or so in the
file in the post, so anyone who is trying to help you can reproduce the
error.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp