Aaron Reynolds

… the ramblings of a fool

SqlBulkCopy inserting all Nulls using ODBC

Today I was trying to import a set of numbers through a system I’ve used hundreds of times but kept finding that although thousands of rows were being imported, they were all full of Null values – I mean ALL of the values were Nulls.

At first I looked for a fault in my program and then the T-SQL stored procedures I was using – they were all fine.

Then I checked the schema.ini file that was generated in line with http://msdn.microsoft.com/en-gb/library/windows/desktop/ms709353(v=vs.85).aspx and that was also fine.

Then I looked at the data but couldn’t initially figure out why the data was being misread. When I checked against other data files that should have had the same or similar file size I found this particular file to be rather larger than it should have been. When I opened it in Notepad and did a “Save As…” I could see it was saving it as “Unicode” and that is where the problem lay. I saved it as “ANSI” (but could have also used “UTF-8” I think) and the file imported as normal.

Job done.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.