I wasn’t able to find out what we were missing. I didn’t even had to try reproduce this error in my own instance it came instantly so I googled Binged (sorry! J). Among other forum posts there were ones which said that MSDASQL is not reliable at all and this error ‘just happens’.

Workaround – better solution

I thought whether I could use some kind bulk operation on this. Luckily yes! Here comes the query

SELECT *
FROM OPENROWSET(BULK 'c:\temp\test.txt', FORMATFILE= 'c:\temp\test.xml') AS a
WHERE col0 LIKE N'%doe%'

C:\temp\test.txt = my file for import

C:\temp\test.xml = XML schema Format file for test.txt

test.txt content:

test_content_col0;test_contenct_col1john doe;malejane doe;female

You might wonder what FORMATFILE is for. It’s schema of your data for BULK operation. It has to be used when number of columns in data file differs from target table, columns are in different order, custom delimiter is used or this case – when you use BULK within OPENROWSET.

You can write your own schema ‘by hand’ based on example bellow of my dataset which has two columns named col0 and col1 both nvarchar(255):

Or you can just design (use) your (existing) table in SQL Server Management Studio

..and create schema based on the table with BCP command utility.

About BCP

Bulk Copy program (BCP) is a command line utility used to import/export data. BCP became available in Microsoft SQL Server 6.0 in which it was the tool of choice for import/export of data. With Microsoft SQL Server 7.0 came first version of Data Transformation Services (DTS) since SQL 2005 Integration Services (SSIS) which offered much more flexibility and comfort then BCP. Nowadays BCP is used for large dataset loads (by default BCP ignores constraints and does not fire triggers which makes it even more faster) or just loads which does not require additional data transformation. BCP is also able to create a Format file for BULK INSERT operations. For more information check BOL, bcp Utility article.

To create format file open cmd shell and execute following script:

bcp Test.dbo.import format nul -c -x -f import.xml -t; -T

Test.dbo.import – schema will be created based on table import at dbo schema at Test database

I also should add that for a durable solution, bcp is the better way to go but constructing a format file is only fun if you’re the type of person who likes to light themselves on fire. And it is only better because of the nasty things Access does to your database server when it is given the chance. Apparently SQL Server and Access are distant cousins.