Bulk Insert / (type mismatch or invalid character for the specified codepage)

Question

Dear friends, I need your help and expertise. I'm trying to import several log files into a table but after I created a configuration file, I can't import a single record, because for some reason this doesn't understand my datetimes (2 values) saying that something is incorrect, an invalid character, I tried to remove the time, format the date sql like... nothing seems to work... in the following image, I have included my table structure, the 1 record data, the configuration file, my bulk insert query and my ugly result... my version of sql is 2008 (not R2)

Answer 1

You have the format file wrong. You have a text file, so all data types in the file should be SQLCHAR (ANSI code page) or SQLNCHAR (Unicode file). SQLDATETIME etc is only used when you have native types, that is binary data.

Note that if you have a Unicode file, you need to account for that with the separators.

I have an article on my web site entitled Using the bulk-load tools in SQL Server that can give you further guidance.