SQL Server 2000 to MySQL Databases

[demodav]

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.

[rmiao]

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

[demodav]

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.

[rmiao]

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

[demodav]

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?

[MAK]

On the host of SQL Server

click Start-Run-
type Services.msc

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

[demodav]

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

[MAK]

yes. it started

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


SET ARITHABORT ON

[rmiao]

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

[demodav]

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?

[demodav]

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.

[rmiao]

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

[demodav]

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.

[rmiao]

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

[demodav]

How do I make it do a distributed transaction?