MySQL Bugs: #24580: Excel query can't be redirected to different server using ODBC 3.51.12

Bug #24580	Excel query can't be redirected to different server using ODBC 3.51.12
Submitted:	24 Nov 2006 23:21	Modified:	5 Apr 2007 5:57
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	Excel

Description:
I have an excel spreadsheet with an SQL query using ODBC driver v3.51.06 to MySQL v4.0.20a database on machine "production-server". 

In the process of testing a server upgrade I was unable to change the ODBC driver from v3.51.06 to 3.51.12 underneath the already configured Excel spreadsheet SQL query

When the excel sheet was opened after the upgrade it tried to communicate to my production server and failed, I reconfigured it to point to my test server but again it failed.

I a not able to re-direct the spreadsheet to the new server usign ODBC v3.51.12
BUT
When I try the same steps with previous ODBC driver 3.51.11.2 it works!

How to repeat:
1) Configure a datasource named "Production" using ODBC v3.51.06 to server "one"

2) Setup an SQL query in Excel using the datsource

3) Verify that the excel query retrieves data

4) Close excel

5) UnInstall the ODBC driver!

6) Install the latest ODBC driver v3.51.12

7) Make sure that the server "one" is no longer accessible on the network for the test

8) Configure a datasource named "Production" using ODBC v3.51.12 to a ***different!*** server "two"

9) Open the excel file, it uses the new ODBC driver but still tries to connect to the original server "one" and fails

10) At the ODBC Datasource configuration dialog that pops up, reconfigure the target host to the test server "two"

11) It still fails and I an not able to re-direct the spreadsheet to the different server!

Hi and thanks for your problem report.
Can you please provide/check the following:
  - Excel version
  - Jet engine version
  - 2) Setup an SQL query in Excel using the datsource - File DSN or System DSN
  - Make sure all of MyODBC3* files in %winsysdir% are 3.51.12, driver (myodbc3.dll) should be about 1.5MB

Excel saves DSN name and SQL info inside sheet so there might be problem there.

Excel versions (it fails in both)
   2000 SP3
   2002 SP3

Jet engine version did not change between the use of v3.51.06, 3.51.12 and 3.51.11-2!

Jet engine v4.0

Hi, what of other questions:
  - 2) Setup an SQL query in Excel using the datsource-*File DSN* or *System DSN*
  - Make sure all of MyODBC3* files in %winsysdir% are 3.51.12, driver
(myodbc3.dll) should be about 1.5MB

I am unable at this time to do that test... I have a work around using 3.51.11-2 and do not have the time right now to uninstall it, install 3.51.06. Then setup the SQL query, move to a test environment, uninstall 3.51.06, install 3.51.12 and attempt re-configuration to test server.  And I did not want to polute the post with this description either.

And why would it work with 3.51.11-2 but not 3.51.12?

Hi Mikey,

The first part of your problem can be easily explained by the Excel practice of saving the connection information as part of the sheet into which you embed your query.  If you get curious, you might want to open up a small spreadsheet in a text editor (like notepad) and try to see what recognizable information you can read directly from a saved query. 

The second part could have simply been due to a rebuild of your query as you were converting your drivers. By rebuilding or refreshing your query you could have captured a fresh set of connection information from the DSN using your 3.51.11-2 driver.  There are many hoops that Excel asks us to jump through to embed a set of query-based data into our sheets and any of them may have triggered a reload of your DSN information. 

I am not denying the possibility of a bug, but I strongly suspect Excel as the primary contributor to the behaviors you are noting in this report.

We have provided more info on how Excel manipulates DSN data in Bug#16653, please see the discussion on editing .ODC files / Excel sheet there. This is a feature request for MS not MySQL.