MSSQL7 DB replicated to MySQL DB?

[Greg Larkin]

Hi,

I am trying to figure out a way to link a MySQL database (running on Linux) to my MSSQL7 database. My ultimate goal is to synchronize inserted/updated/deleted data in the MSSQL7 DB to the MySQL DB.

I have tried a few things and have had a few ideas. They are:

1) Create table triggers in the MSSQL7 DB that will synchronize the data to the MySQL DB. I can't figure out how to run Transact/SQL statements (INSERT, UPDATE, DELETE, etc.) into an ODBC DSN pointing to the MySQL DB. I think this could be done with a linked server (see next item).

2) Add a linked server under Security->Linked Servers in the MSSQL7 Enterprise Manager. I have managed to get the remote MySQL server to link in here. I can even see the table names in the server browser, so that's encouraging! However, I cannot figure out the syntax of the fully-qualified table names in my SELECT statements. I have tried this:

SELECT * from LinuxBox..MyDB.MyTable
-and-
SELECT * from LinuxBox.MyDB.MyDB.MyTable

but I get:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.


This statement:

SELECT * from LinuxBox...MyTable;

gives me:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.


Since I can see the table names in the linked server browser in Enterprise Manager, though, some kind of connection must be active. I just need to figure out how to refer to the remote tables.

A big problem is that when I supply a catalog name while defining the linked server, I get an error message from the Enterprise Manager:

Error 7399: OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.

Therefore, I can't define a catalog with the linked server. I think a catalog is required for the "4-part name" to work in the SELECT statement above.

3) Write an extended stored procedure that uses the ODBC function library to make a connection to the MySQL DSN and have a table trigger call the ESP. Hoping to be able to do something simpler than this!

Any suggestions would be greatly appreciated!

Thanks,
Greg

[Ray Miao]

try 'select * from LinuxBox.MyDB.owner.MyTable'.


------------
Greg Larkin at 1/4/00 3:58:32 PM

Hi,

I am trying to figure out a way to link a MySQL database (running on Linux) to my MSSQL7 database. My ultimate goal is to synchronize inserted/updated/deleted data in the MSSQL7 DB to the MySQL DB.

I have tried a few things and have had a few ideas. They are:

1) Create table triggers in the MSSQL7 DB that will synchronize the data to the MySQL DB. I can't figure out how to run Transact/SQL statements (INSERT, UPDATE, DELETE, etc.) into an ODBC DSN pointing to the MySQL DB. I think this could be done with a linked server (see next item).

2) Add a linked server under Security->Linked Servers in the MSSQL7 Enterprise Manager. I have managed to get the remote MySQL server to link in here. I can even see the table names in the server browser, so that's encouraging! However, I cannot figure out the syntax of the fully-qualified table names in my SELECT statements. I have tried this:

SELECT * from LinuxBox..MyDB.MyTable
-and-
SELECT * from LinuxBox.MyDB.MyDB.MyTable

but I get:

Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.


This statement:

SELECT * from LinuxBox...MyTable;

gives me:

Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.


Since I can see the table names in the linked server browser in Enterprise Manager, though, some kind of connection must be active. I just need to figure out how to refer to the remote tables.

A big problem is that when I supply a catalog name while defining the linked server, I get an error message from the Enterprise Manager:

Error 7399: OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.

Therefore, I can't define a catalog with the linked server. I think a catalog is required for the "4-part name" to work in the SELECT statement above.

3) Write an extended stored procedure that uses the ODBC function library to make a connection to the MySQL DSN and have a table trigger call the ESP. Hoping to be able to do something simpler than this!

Any suggestions would be greatly appreciated!

Thanks,
Greg