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?
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.
Last edited by demodav; 02-11-2009 at 02:44 PM.
Reason: Updating Information
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?
Last edited by demodav; 02-12-2009 at 11:04 AM.
Reason: Updated information
On the host of SQL Server
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]
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?
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.
Last edited by demodav; 02-18-2009 at 10:29 AM.
Reason: updated the info.
Can set linked server doesn't mean can run distributed transaction.
How do I make it do a distributed transaction?
Tags for this Thread
DatabaseJournal Recent Articles
Executing SSIS Packages Using DTExec
What Every DBA Ought to Know About SQL Ser...
Redmond exploits MySQL uncertainty
Oracle Launches Oracle Global Trade Manage...
SQL Server 2008 RTM Support Ends April 13,...