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

Question

If I execute 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 with:

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

I'm running Microsoft SQL Server 2008 R2 on Win 7 x64. I've also tried it on Windows Vista x32, same error.

Questions:

1. Has anyone successfully run this command on Win 7 x64?
2. Do any of you know what could be causing the T-SQL command to fail like this?

---

Update 1:

If you get an error that mentions "ad hoc queries", run the following to eliminate 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 an out-of-the-box Win7 x64 machine, but I still can't fix this error on my machine. I'll go with "bulk insert" (see my comments below).

Answer 1

Solution: I've just wrestled with this issue for several hours on a Win7 x64 machine, and it is so difficult to find a good answer online that I thought I'd contribute one to this thread belatedly.

On my machine (Win7, x64, SQL Server 2008 R2), Administrative Tools > Data Sources (ODBC) > Drivers, shows no driver called "Microsoft Text Driver". But there is a driver labeled "Microsoft Access Text Driver (*.txt, *.csv)"

I was able to change the driver name in code similar to the original questioner's INCLUDING the parentheses (*.txt, *.csv) WITH a comma 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 in specifying the drive must be exactly the same. I can vouch for that because I went through several wrong iterations.