CODEWITHSUNDEEP
sql-serverwindows-server-2008
Aron
Aron

Reputation: 3569

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:

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!

Upvotes: 1

Views: 6034

Answers (2)

MarianoC
MarianoC

Reputation: 351

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.

Upvotes: 1

Aron
Aron

Reputation: 3569

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.

Upvotes: 2

Related Questions