The OLE DB provider "MSDASQL" has not been registered

Question

I'd like to extract data from a .csv file. I cannot use OpenRowSet on a 64-bit machine because it says:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" has not been registered.

and there is no MSDASQL entry in the Linked Servers -> Providers section of the SQL Server 2005 SSMS the same way 32-bit ones have.

Can someone suggest a comparable method to extract data from a .csv file?

Answer 1

Are both servers 64 bit?

You have you install 64-Bit OLEDB Provider for ODBC (MSDASQL). We did to get our linked servers working (non-SQL Server). And more info here

If not 64 bit, then it could be an OS corruption because it's just there on 32 bit Windows.

Edit:

Have you actually checked if the servers are 64 bit, or the broken one is, using select @@version, or simply ignoring potential fixes based on personal experience and knowledge...?

Answer 2

This is how to use OPENROWSET to extract data from a .csv file on a 64-bit computer. You railroad through a 32-bit computer, here called LinkedServer32Bit, via OPENQUERY:

SELECT * FROM OPENQUERY
(
    LinkedServer32Bit, 
   'Select * FROM OPENROWSET 
    (
       ''MSDASQL'', 
       ''Driver={Microsoft Text Driver (*.txt; *.csv)}; 
         DefaultDir=C:\z\;'', 
       ''SELECT y FROM x.csv''
    )'
)