SQL Stored Procedure for bulk insert error MSG 241

Question

Background: Attempting to bulk insert data from flat file source into database by bulk insert. Gives following.

Error

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

Example row from data:

2016-01-14 00:00:00         GTM         clocktic

Format file (.xml)

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LEN GTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="TStamp" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="2" NAME="var2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="var3" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="var4" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="var5" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="var6" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="var7" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="8" NAME="var8" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="9" NAME="var9" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="10" NAME="var10" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="11" NAME="var11" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="12" NAME="var12" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="13" NAME="var13" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

refer this:http://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i — TheGameiswar, Mar 04 '16 at 13:30

TT. · Answer 1 · 2016-03-04T14:21:12.970

0

Try SET DATEFORMAT ymd; before doing the import.

If that doesn't work:

Have the export process export date/times in a format that SQL Server can parse: YYYY-MM-DDThh:mm:ss[.mmm] (literal character T in the middle) or YYYYMMDD[ hh:mm:ss[.mmm]] (cf DATETIME)
Import the data to a staging table, then insert to the final table by converting the varchar to a datetime.
Use a different way to import the data, eg SQL Server Integration Services.

edited Mar 04 '16 at 14:21

answered Mar 04 '16 at 13:57

TT.

15,774
6
47
88

Tried setting dateformat to ymd, when i tried your first suggestion ( in a temp data of 10 rows, i got 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 (Id). ) – Froxer Mar 04 '16 at 15:08
@Froxer Probably because the collation on the temp table is not the same as in your format file? – TT. Mar 04 '16 at 15:09
I dont get it: 1 column from database : [Objekt] [varchar](20) COLLATE Finnish_Swedish_CI_AS NULL, They do use the same codepage? – Froxer Mar 04 '16 at 15:48

score 0 · Accepted Answer · answered Mar 04 '16 at 18:43

0

Solved it by creating a .fmt file(Had a XML Format file prior to it) and directing data into proper columns. a solution does not always have to be hard. Just need to get things done properly from the get go!

answered Mar 04 '16 at 18:43

Froxer

124
1
3
12

SQL Stored Procedure for bulk insert error MSG 241

2 Answers2