I am using:
* Windows 7
* 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
Catastrophic failure. A connection could not be made.
**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 Office and PowerPivot and MySQL ODBC 5.1 instead of 5.2, 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. Not sure what is different on that machine to enable PowerPivot to work.
Any advice appreciated!