[dba-SQLServer] Bulk insert failing

jwcolby jwcolby at colbyconsulting.com
Sat Jan 30 15:27:09 CST 2010


I have created an XML format file for a table using BCP to create the format file.
I have placed that in a known path.
I have created a stored procedure which builds a dynamic SQL Statement to perform a bulk insert 
using the format file.

My TSQL is as follows:

BULK INSERT [_DataDepotEmail].[dbo].[ANK_Recovered_Raw]
					FROM 'D:\UD37_M12_NCOA_OP.TXT'
					WITH (FORMATFILE = 'D:\ANK_Recovered_Raw.xml')

I received the following error:

There was a BCP error IMPORTING data into _DataDepotEmail
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Unfortunately this is a generic "something went wrong" error message that SQL Server is so famous for.

It just occurred to me to cut and paste the dynamic SQL above into a query window and run it and I 
get a further error message:

"The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the 
field terminator and row terminator are specified correctly."

So apparently it is attempting to use the format file to open the file.  I guess.

The format file says the first field should be 80 characters, which it is.

I am pretty much stalled at this point.  If anyone has any ideas I would be most grateful.

-- 
John W. Colby
www.ColbyConsulting.com



More information about the dba-SQLServer mailing list