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.
MSDTC? And OS? Google Search please.
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.
MySql is on our webserver hosted by HostMySite.com.
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.