SQL Server repeated error with OPENROWSET

I am running the following query to import a csv file:

SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=D:\Documents\;', 'SELECT * from file.csv') AS something

I am getting this error:

  • Store user data in MVC 2
  • SQL Server: Could not find type in the assembly
  • SQL Server unique constraint problem
  • Must declare the table variable “@temp”
  • Find last sunday
  • Microsoft.SqlServer.Management namespaces/references in distributed application?
  • 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)".
    

    This is what I have done to try an resolve the issue, neither of which was enough:

    1. MsSQL and IIS have access to the folder and file in question. They also both have access to the temporary folder for Network Services under which SQL server is running.
    2. I have enabled Ad Hoc Distributed Queries using sp_configure
    3. I have fixed the registry key DisallowAdhocAccess by setting it to 0
    4. I have installed Microsoft Access Database Engine 2010 Redistributable

    The system is a Windows 2008 Server running SQL Server 2012 Express, but the issue has been replicated with SQL Server 2008 as well.

    At this point any random ideas are appreciated :). Thanks!

  • Force T-SQL query to be case sensitive in MS
  • SQL Server : query to get MINUTE of date in specific date
  • Error on the multi-part identifier could not be found
  • Forcing SQL Server to pre-cache entire database into memory
  • Most efficient way to return a fixed number of values in SQL
  • How do I properly access temp tables created in another database?
  • 2 Solutions collect form web for “SQL Server repeated error with OPENROWSET”

    The answer turned out to be quite simple, but very much underdocumented: the MSDASQL driver did not work together well with a 64bit installation of SQL Server.

    There is some more info about this in this thread but for my purposes, “downgrading” to a 32bit instance was a sufficient solution.

    It Works. Just make sure tha name of the driver is EXACTLY as it shows in control panel ODBC Data Sources (64 bits). In my case, the correct name is “‘Driver={Microsoft Access Text Driver (*.txt, *.csv)}”, I use it in my machine with Windows 8.1 64 bits.

    Regards,

    MarianoC.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.