I am using:
* Windows 7 Professional, 4.00 GB RAM, 32-bit operating system
* Microsoft Office Professional Plus 2010, v14.0.6112 (32-bit)
* MS SQL Server 2012 PowerPivot for Excel 32-bit, v 11.0.2100.60
* MySQL Connector/ODBC 5.2
I can create an ODBC connection to MySQL with no problems. And I can even use this ODBC connection from the Data tab in Excel (not PowerPivot) to get data from Other Source, either by pulling a single table from MySQL database or editing the connection properties/definition to provide a query (command text) that joins multiple tables.
However, in PowerPivot...
1) If I try to get data from a SQL Server connection, I get an error:
Cannot connect to the data source because the SqlServer provider is not installed
2) If try to get data from a text file, I get an error:
Details: Failed to connect to the server.
Reason: Provider information is missing from the connection string
3) If I try to create a connection From Other Source/ODBC, I get an error:
ERROR [HY010] [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.25a-27.1-log] or
Test connection failed b/c of an error in initializing provider. Catastrophic failure.
**When I click for the dropdown list of databases in the Data Link Properties, the connection is made and list is shown.
On a different machine with the same versions of Windows, Office and PowerPivot, I am able to get PowerPivot to work just fine. Note that in both the ODBC connection and Data Link in PowerPivot, the Initial Catalog must be left blank for the MySQL data pull to work.
Machine that works is 64-bit virtual image with MySQL ODBC 5.1 driver and user has admin rights. Machine that does not work is 32-bit laptop with MySQL ODBC 5.2 driver and user does not have admin rights.