Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24

Thread: SQL Server 2000 to MySQL Databases

  1. #16
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  2. #17
    Join Date
    Feb 2009
    Posts
    27

    Query

    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?

  3. #18
    Join Date
    Sep 2002
    Posts
    5,938
    Did you update MySql via openquery? Able to read doesn't mean able to write.

  4. #19
    Join Date
    Feb 2009
    Posts
    27

    Query

    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?

  5. #20
    Join Date
    Sep 2002
    Posts
    5,938
    Don't use trigger if dbs are not required to be sync in real time, since trigger slows down process on source db.

  6. #21
    Join Date
    Feb 2009
    Posts
    27

    Ok now what?

    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.

  7. #22
    Join Date
    Feb 2009
    Posts
    27

    Stored Procedure?

    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?

  8. #23
    Join Date
    Feb 2009
    Posts
    27

    C#?

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

  9. #24
    Join Date
    Feb 2009
    Posts
    27
    How would I go about updating my MySQL Database everytime something changes in the SQL?

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
  •