Microsoft SQL Server forum - Type Mismatch Error On Bulk Insert

Author

Type Mismatch Error On Bulk Insert

Mike

2005-10-27, 9:24 am

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

2005-10-27, 9:24 am

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

2005-10-27, 9:24 am

Mike (noway@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, esquel@sommarskog.se

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

Mike

2005-10-27, 9:24 am

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" <esquel@sommarskog.se> wrote in message
news:Xns96EEED971F19
3Yazorman@127.0.0.1...
> Mike (noway@forgetit.com) writes:
>
> 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, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

Erland Sommarskog

2005-10-27, 9:24 am

Mike (noway@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, esquel@sommarskog.se

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

Sponsored Links