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

MS SQL to MySQL

I successfully connected an MS SQL Server database to a MySQL database.  The SQL Server db is for internal access and I set up the MySQL database for external access.  So, as the internal db is updated, that same live data is pushed to the external database via triggers and SQL jobs.

Whenever a certain table is updated or a row is inserted, a trigger fires, and that new or updated row is inserted into a temporary table.  I created a SQL job that runs every two minutes that will copy new rows (rows where date < getDate() and has not been copied to the external db) to the external db.  During the wee hours of the night/morning, the rows, from that temporary SQL table, that have been written to the external db are dropped.  That way, we don’t have a bloated temporary table on our hands.