I am running MSSQL 2008 R2, as well as MySQL 5.6. Both database servers are running on the same Windows Server 2008 machine. I have had a linked server set up working fine for a few years, but suddenly I have started to receive the following error:
OLE DB provider "MSDASQL" for linked server "MYSQL_LINK" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL_LINK".
I have read through a few other similar questions on Stack Overflow, but so far none of them seem to be the same problem. Like I said, this setup has been working fine for years, and nothing has changed with the configuration. The server was rebooted this morning, and the linked server is no longer working.
Other than the error above, I am not sure exactly what the problem is. I have checked that the ODBC connection is set up on the server, which it is. However, when I try to click on "configure" the ODBC connection, nothing happens. I think perhaps the ODBC connection got screwed up somehow?
Any suggestions are greatly appreciated!
EDIT:
I have seen it mentioned that there are some issues on 64-bit systems. The server is running 64-bit Windows Server 2008. However, I don't think this should be the issue, as I mentioned that this has been working without issue for approximately three years. I suspect it may be an ODBC issue, but I am hoping someone may have information as to why, or what could be tried to remedy this.
I'm not sure what the cause was, but what I ended up doing was re-installing the MySQL ODBC driver, and the current linked server set up started working again! Very bizarre, as the current set up has been running fine with no issues, but at least it is working now.