Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: Configuring Linked Server (mysql)

    Hi,

    I have setup a MYSQL server as a linked server in SQL SERVER 2000. I am able to select from a table using OPENQUERY as I understand that you can't use the 4 part qualifier to access an object from MYSQL as the ODBC driver does not expose the proper interface for this. The problem is that I cannot update,delete or insert into the OPENQUERY() table

    eg.
    UPDATE OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') SET id = 0

    INSERT INTO OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') VALUES(0)

    DELETE FROM OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') WHERE id = 0

    This is the OLEDB PROVIDER FOR ODBC error I am getting...

    The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].

    I know there are brilliant Professionals on this forum. Please tell me that this is just an MDAC upgrade or something that is not configured correctly...

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Linked server query must be supported and contained a pass-thru query (OpenQuery, or OpenRowset with query a inside it).
    SELECT/INSERT/UPDATE/DELETE statement against a 4-part name for which SQL Server generates a query against the provider for optimization.

    And see whether this KBA http://support.microsoft.com/default...;en-us;q270119 is any help.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    Thanks for the article reference. I had a look at it and it is definitely a different error that is explained in it. As I said above, you can't use 4 part names to access objects in mysql as the odbc driver does not allow it. Does anyone at all know why this error would be causes as MS does not document the reasons for it. Remember, I can select from the openquery but not update,insert,delete...

Posting Permissions

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