FAQ: Why cannot 64-bit MSDASQL access a .csv text file?

  • 發行項

Summary

 I have installed 64-bit MSDASQL on my Windows Server 2003, https://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en, and configured a linked server on my 64-bit SQL Server 2008 instance to access a local .csv file, however the following query does not work:
select * from OpenRowset('MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:\Test;Extended properties=''Format=Delimited(,);''','select * from test.csv')

 

Symptom

 The error message is as follows:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

 

Resolution

 The cause of this issue is that there is no 64-bit ODBC text driver installed on your 64-bit Windows server 2003 actually. The 64-bit MSDASQL just provides an OLEDB/ODBC 'bridge' that allows applications built on OLEDB and ADO (which uses OLEDB internally) to access data sources through ODBC drivers.

 To resolve this issue, you need to first install a 64-bit ODBC text driver. Previously this is not possible, but now it has been included in "Microsoft Access Database Engine 2010 Redistributable",  https://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en.

 After you install this driver, you should see "Microsoft Access Text Driver (*.txt,*.csv)" with ACEODBC.DLL in your 64-bit ODBC Data Source Administrator. Then you can run your query again.

Comments

  • Anonymous
    November 16, 2011
    The comment has been removed

  • Anonymous
    May 20, 2012
    Is the same solution applicable for SQL 2008 which is running with Windows 2008 ? Thanks in Advance...

  • Anonymous
    October 03, 2012
    I had the same problem, but when I compared names of ODBC drive for text, I realised that it's name is {Microsoft Access Text Driver (*.txt, .csv)} and not {Microsoft Text Driver (.txt; .csv)} When I changed it, my sql command worked: select * from OpenRowset('MSDASQL', 'Driver={Microsoft Access Text Driver (.txt, *.csv)};  DefaultDir=c:temptest','select * from file.csv')

  • Anonymous
    January 15, 2013
    can someboday help me to link my myob databse into SQL server 2008 64bit, suresh@abc.lk

  • Anonymous
    April 19, 2013
    Thanks Petrpan, that solved it for me"

  • Anonymous
    May 08, 2014
    The comment has been removed