Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: SQL Server 2000 to MySQL Databases

  1. #1
    Join Date
    Feb 2009
    Posts
    27

    Post 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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Is msdtc service running on sql server? What's os version on sql server?

  3. #3
    Join Date
    Feb 2009
    Posts
    27

    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

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    You have to check that in services applet, ask your win admin for help.

  5. #5
    Join Date
    Feb 2009
    Posts
    27

    Win admin?

    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

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    On the host of SQL Server

    click Start-Run-
    type Services.msc

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

  7. #7
    Join Date
    Feb 2009
    Posts
    27
    I checked that, it says that the status was started? That means it is running right?

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    yes. it started

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


    SET ARITHABORT ON

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    If os is win2k3, you have to enable network access in msdtc.

  10. #10
    Join Date
    Feb 2009
    Posts
    27

    My Trigger

    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?

  11. #11
    Join Date
    Feb 2009
    Posts
    27
    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.

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    Ignore that part in win2k. Where's MySql by the way? On another windows server?

  13. #13
    Join Date
    Feb 2009
    Posts
    27

    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.

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    Can set linked server doesn't mean can run distributed transaction.

  15. #15
    Join Date
    Feb 2009
    Posts
    27

    Please Explain.

    How do I make it do a distributed transaction?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •