SQL Server 2000 to MySQL Databases

Ok we are trying to link two databases and their tables we are following the steps in http://developer.infi.nl/daniel/Link...nd%20MSSQL.pdf. I have successfully linked up the databases, but what I have a problem with is in Page 8 in the above link was to set up a trigger that when one table gets changed the linked server would also change.

When I tried this I got this error and got stumped?

SQL Server Enterprise Manager Error reads:


[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: [MySQL][ODBC 3.51 Driver] Optional feature not supported]
[Microsoft][ODBC SQL Server Driver][SQL Server] The operation could not be performed because the OLE DB provider ‘MSDASQL’ was unable to begin a distributed transaction.

Is msdtc service running on sql server? What's os version on sql server?

I am new to the whole server environment so please be patient. How do you find out if you have msdtc service running on the server? I thought sql server 2000 was good enough, but I have SQL Server 2000 5.00.2195 SP4 and am running Version 8.00.194 of SQL.

You have to check that in services applet, ask your win admin for help.

Win admin? There's no win admin I'm it. I went to the services applet by typing in the run bar SERVICES.MSC and I looked and did not see MSDTC running in a service on my machine do I need to have it running where do I get it and how do I run it?

On the host of SQL Server

click Start-Run-
type Services.msc

Look for "Distribution Transaction Co-ordinator". See if that service is running.

I checked that, it says that the status was started? That means it is running right?

yes. it started

Add this statement before in the begining of the trigger and try executing your insert statement again.


SET ARITHABORT ON

If os is win2k3, you have to enable network access in msdtc.

Here is my trigger


CREATE TRIGGER test_insert ON [dbo].[test]
FOR INSERT
AS
SET XACT_ABORT ON
INSERT INTO OPENQUERY(MYSQL, 'SELECT * FROM test')
SELECT a, b, c FROM INSERTED

I replaced the SET XACT_ABORT ON for SET ARITHABORT ON.

But Still got the same error?

rmiao how do you enable network access in msdtc? Also I said that my os is 2000, I do not know what win2k3 is, is that Windows 2003? No I have windows 2000.

Ignore that part in win2k. Where's MySql by the way? On another windows server?

The MySQL is on another server, but I have been able to successfully link up the servers. I can see that the tables exist from my MySQL server on the Windows 2000 machine. But when I tried to set up the trigger is when I got the error.

I set up the trigger by going to my table [test] in the SQL server right clicking it and manage triggers and pasted the code above in the trigger. But when I tried to insert something is when I got the error.

Sorry I forgot, the MySQL is on a linux server.

Can set linked server doesn't mean can run distributed transaction.

How do I make it do a distributed transaction?

From books online:

The types of queries that are supported against linked servers depend on the level of support for transactions present in the OLE DB providers. OLE DB defines two optional interfaces for transaction management:

-ITransactionLocal supports local transactions in the OLE DB data source.
-ITransactionJoin lets the provider join a distributed transaction that includes other resource managers.

Any provider that supports ITransactionJoin also supports ITransactionLocal.

If a distributed query is executed when the connection is in autocommit mode, these rules apply:

-Only read operations are allowed against providers that do not support ITransactionLocal.
-All update operations are allowed against any providers that support ITransactionLocal.

You have to check with MySql to ensure its odbc driver supports ITransactionLocal.

I am able to run a queries using openquery(MySQL

for the linked server and my openquery works, why would my trigger not work any ideas?

Did you update MySql via openquery? Able to read doesn't mean able to write.

I was able to successfully update the mysql database using this query.

UPDATE OPENQUERY (MySQL, 'SELECT b FROM test WHERE a = 3')
SET b = 'test';

Is there a way to just set up queries to insert, update, delete the MySQL Database and not even use triggers? Or are triggers more convenient?

And how would one execute queries on let's say a nightly basis, If I were to make them?

Don't use trigger if dbs are not required to be sync in real time, since trigger slows down process on source db.

Ok then, if I don't use triggers, how would I go about updating my MySQL Database everytime something changes in the SQL?Be specific please, I am new to the Server side stuff.

I assume that I could posible write a stored procedure to do this?

The problem I have is no idea where to begin?

What I need to do is to migrate data perioadically I can do this already, then compare the data with the mysql table using an openquery command and update and insert what I need to.

But this is way to complicated for me to comprehend right now. Help please.

How would you loop through a table and compare them using openquery and insert and update what needed?

I also have the ability to write in C# if that will work?

How would I go about updating my MySQL Database everytime something changes in the SQL?