Error: "OLE DB provider" MSDASQL "for linked server" (null) "returned message" [Microsoft] [ODBC Driver Manager] Data source name not found ... "

Question

If I run the following command:

select * from OpenRowset ( 'MSDASQL', 'Driver={Microsoft Text Driver (*.txt;*.csv)};DefaultDir=C:\;', 'select top 10 * from C:\x.csv' ) 

... then Microsoft SQL Server Management Studio responds:

[Microsoft] [ODBC Driver Manager] Data source name not found, and no default.

I am running Microsoft SQL Server 2008 R2 on Win 7 x64. I also tried this on Windows Vista x32, same error.

Questions:

• Has anyone successfully executed this command on Win 7 x64?
• Do any of you know what might cause the T-SQL command to fail?

---

Update 1:

If you receive an "ad hoc queries" error message, follow these steps to resolve it:

 EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO 

---

Update 2:

It works on a finished Win7 x64 machine, but I still cannot fix this error on my machine. I will go over with the "bulk insert" (see my comments below).

Answer 1

I just tried it on x64 Win7 and made it work. I think there are a couple of problems.

• I believe you need to add a space between *.txt; and *.csv
• Do not include path with file name

This worked:

 select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\;', 'select top 10 * from x.csv') 

Answer 2

Solution: I just struggled with this problem for several hours on a Win7 x64 machine, and it is so difficult to find a good answer on the Internet that I thought I would contribute to this issue late.

On my machine (Win7, x64, SQL Server 2008 R2), Administration> Data Sources (ODBC)> Drivers, a driver called "Microsoft Text Driver" does not appear. But there is a driver that says "Microsoft Access Text Driver (* .txt, * .csv)"

I managed to change the driver name in code similar to the original questionnaire INCLUDING parentheses (* .txt, * .csv) with a semicolon and a space, not a semicolon. And it worked.

 select * from OpenRowset ( 'MSDASQL', 'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\;', 'select top 10 * from C:\x.csv' ) 

Note that the syntax for specifying a drive must be exactly the same. I can vouch for this because I went through several wrong iterations.