Issue with BULK INSERT - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Issue with BULK INSERT

Author

Topic

Ash1807
Starting Member

10 Posts

Posted - 2015-03-16 : 13:30:16

I am trying my first bulk update to an existing SWL table from a CSV text file, but getting an error which I am struggling to fathom out.

The text file naming is exacrtly the same as the SQL table, with the same attributes

The statements:
BULK INSERT [Jedox_prod].[dbo].[B_BP_Customer]
FROM 'c:\Baan\jedox_daily\jdcom4401.txt'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
GO

The error message is:
[size=1Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 3 (BP_Country).
Msg 7399, Level 16, State 1, Line 1
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 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".size=1]

The have checked and re-checked the BP_Country field ( the 1st field after the key) and I am not seeing any mismatches.

Any idea, anyone?

Kind regards

Ash Gajjar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-16 : 13:34:54

Make sure the path to the file is relative to the sql server, not your client application.
It also seem that a codepage mismatch is happening.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-16 : 13:35:38

Please post some sample data and the CREATE TABLE statement for the target table.

Ash1807
Starting Member

10 Posts

Posted - 2015-03-18 : 06:04:22

I have cleared all of the errors now, bar one

One of my database column is defined as date/time, and the input data coming is DD/MM/YY.

The Bulk insert is showing error as below.Seems like it is expecting a MM/DD/YY format
conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 13 (LastInvoice)

Is there a setting at either column or database level where I can enable DD/MM/YY.

So far, gatitude for all the assistance this forum is giving me

Ash Gajjar

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-18 : 09:22:34

BULK INSERT should obey the locale setting. Try changing language before the BULK INSERT command:

https://msdn.microsoft.com/en-us/library/ms174398.aspx

huangchen
Starting Member

37 Posts

Posted - 2015-04-02 : 05:55:43

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources