Post reply

Querying MySQL across a linked server

  • December 8, 2006 at 7:26 am

    #92710

    I know that I can use an openquery statement to query my linked server, but is there an easier way?

    It's a MySQL server - I'd love to be able to use a variant of the four-part name, if that's possible.

    Does anyone know?

  • December 8, 2006 at 7:52 am

    #676897

    Okay, I found the following in the help and don't understand how to get all of this information:

    After a linked server is defined, to reference data objects in that linked server, you can use a four-part name in the form linked_server_name.catalog.schema.object_name in Transact-SQL statements. The parts are defined as:

    linked_server_name

    Linked server that references the OLE DB data source.

    catalog

    Catalog in the OLE DB data source that contains the object.

    schema

    Schema in the catalog that contains the object.

    object_name

    Data object in the schema.

    I understand the linked_server_name and the object_name, but where can I see what the catalog and schema names are for the linked server?

    Many thanks -

  • December 8, 2006 at 11:06 am

    #676999

    The catalog is the database and the equivalent of the schema in SQL Server 2000 is the owner. For SS2K5, Oracle, DB2, etc., schema is the schema. It's the collection above the table name.

    Set up the linked server ot point to the MySQL server. Then query as linked servername.database.schema.table.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • December 8, 2006 at 2:52 pm

    #677057

    I'm not having a lot of luck with this, because it's MySQL, I think.

    Here's the error message I get:

    A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

    My guess is that using the four-part name may not be possible. Does anyone have this working with MySQL?

  • May 9, 2007 at 9:50 am

    #705276

    Were you ever able to get the four-part name to work in order to query the MySQL linked server? I'm trying to do the same thing and I getting the same error that you were. I need to use variables to query the data from MySQL and you can't use variables int he OpenQuery function. The only other option that I have found is to use OpenQuery to create a view and then query the view.

    Thanks!


    Wendy Schuman

  • May 9, 2007 at 2:35 pm

    #705392

    No - I never managed to get it to work.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply