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.

Answer 2

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

1. I believe you have to add a space between *.txt; and *.csv
2. Don't include the path with the file name

This worked:

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