MySQL four-part name

All day I’ve been trying to use this four-part name to run queries on my linked server. All day…… Everyone seemed to suggest doing the four-part name instead of OpenQuery.
Every time I tried the four-part name, I would get this error:
“A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.”

Of course when I found someone else with the same problem as me, they hadn’t found a solution.  But…I finally figured it out.  MySQL doesn’t support schemas or owners.  So saying, LinkedServerName.DBName.Owner/Schema.TableName wasn’t working because it wasn’t supported.  I’m too lazy to try to remember my search query that brought me to those forums of people with the same problem as me.  Now, there won’t be an answer to their problem.  Oh well.

Anyways, what ended up working for me was:  LinkedServerName…TableName

Created a linked server to MySQL

Just a little background.  My company is currently running a MSSQL server for internal and external content.  We recently decided to keep the MSSQL server internal and create a MySQL server for external access.  I looked into many ways to push real-time data from the internal SQL server to the external MySQL server.  I finally settled on creating a linked server to MySQL through SQL Server.

So for the past two days, I have been banging my head against my keyboard trying to get this linked server connected.  I’ve finally banged my head at the perfect velocity and angle to create a linked server.  To start off, I searched “mysql linked servers” and followed pretty much every “How To:” I could find.  Everyone seemed to be able to create linked servers like it was child’s play.  The best “How To” I found was this one http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/.

I downloaded the MySQL Connector/ODBC 5.1 download.  Note:  I am a little green when it comes to configuring database connections and pretty much anything outside of writing a SQL query.  First, I installed it on my local machine that I use to remote into the server (the MSSQL box).  I quickly realized that wouldn’t work and didn’t make sense.  Next, I installed it on the SQL Server box, ran through creating a new “System DSN”, and of couse could not connect to the MySQL server.  My next thought was to install the Connector/ODBC on the actual server MySQL was on.  I tested the connection and it worked.  So I continued through the “How To”.  I continued through all the steps of creating a new linked server.  At the end of every “How To” was “… and click OK.  And now you’ve created a linked server.”  PPPfffftttt!!!  How about I kept getting the same error:

“Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “mysqlLinked”.
OLE DB provider “MSDASQL” for linked server “mysql-linkedserver” returned message “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”. (Microsoft SQL Server, Error: 7303)”

Of course, I searched for a solution to this error.  A ton of people ran into this issue to.  Some people found a solution because it was an ISP issue or they banged their heads against a keyboard and the linked server miraculously connected.  I finally came to my senses and installed the Connector/ODBC on the same server the SQL Server client was on.  Again I got that error.  I eventually narrowed it down to a permissions problem.  Every time I tried to create the linked server, I noticed it said ‘root@blahblah-internal-server.com’ instead of the server address I was supplying.  I went to PHPMyAdmin to add a new user.  But this time, I set the host to “Any Host or %”.  So now, MySQL should accept ‘root@blahblah-internal-server.com’.  With this new user addition, I created a new DSN with the new user credentials, and waddayaknow a connection test was successful.

One hurdle down, now it’s back to creating a linked server.  I followed through the rest of the “How To” I was using and, voilà, I created a linked server!  It was simple as pie to create a linked server.  With the creation of my first linked server in my pocket, I queried the test database I set up on the MySQL server, and it worked fine.