Can't identify reason for BULK INSERT errors
I'm trying to run this query (I also tried it without specifying FIELDTERMINATOR and ROWTERMINATOR). It's using a datafile that I am manually creating beforehand (not with bcp out).
BULK INSERT FS.dbo.Termination_Call_Detail
FROM 'C:\Termination_Call_Detail__1317841711.dat'
WITH
(
FORMATFILE = 'C:\Termination_Call_Detail__update_TerminationCallDetailData.fmt',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\r\n'
)
The errors I'm getting:
Server message number=4864 severity=16 state=1 line=1 server=USA109MSSQL1A
text=Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 9 (RouterCallKey).Server message number=4832 severity=16 state=1 line=1 server=USA109MSSQL1A
text=Bulk load: An unexpected end of file was encountered in the data file.Server message number=7399 severity=16 state=1 line=1 server=USA109MSSQL1A
text=The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Server message number=7330 severity=16 state=2 line=1 server=USA109MSSQL1A
text=Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I'm trying to get it working with just 1 row.
ASCII datafile:
611658275664 1 5027 5001 5005 5010 2011-10-05 14:53:44 1317840824 240 2011-10-05 14:54:04 1317840844 150026 211 2 1 4480 21543 28 0 29 0 0 0 0 29 0 开发者_StackOverflow中文版 0 0 IPT_Call TAC_Rollover 424755/4 N 0 5030 5000 2865 805336626 2674794299 N 1 3 N 0 0
Hex dump of the datafile: http://pastebin.com/grnwQrdT
The format file: http://pastebin.com/WwSQvCGg
The table schema: http://pastebin.com/gFjp4xYy
This insert is using 66 columns; the table contains 84 columns. If I create a format file with bcp of the whole table, and a datafile of this same row, I can run this BULK INSERT QUERY without a problem.
Hex dump of a datafile created by bcp on the same row of data (with all the columns): http://pastebin.com/MpMk6iq4
I meticulously looked for any missing newlines or tab delimiters, but I cant find any. You can see on the hex dump of the datafile that everything looks properly delimited. I compared it to a datafile created by bcp and couldn't find any formatting differences.
I also cant imagine why its complaining about that particular field (RouterCallKey) - there are other fields above it in the format file that are the same type (like RouterCallKeyDay).
Thanks!
Wouldnt you know it, I figure it out right after I get frustrated enough to post the question. There was a logical bug elsewhere in the code that created the datafile that was feeding this bulk insert query the wrong form
精彩评论