Post reply

Querying remote database

  • November 8, 2007 at 3:37 pm

    #62979

    I am trying to query a remote database using the 4part name for the server.dbase.owner.object but getting an error I can't resolve.

    here is the code I'm using..

    select * from mysql.database.dbo.cust702

    mysql - servername (PS, the server name is a coincidence.. I'm not using MSQL as the database, but rather am using MSSQL 2000 ... sorry for the confusing coincidence!)

    database - database name on server

    dbo - database owner

    cust702 - table name

    The error I get is..

    Server: Msg 7314, Level 16, State 1, Line 1

    OLE DB provider 'mysql' does not contain table '"database"."dbo"."cust702"'. The table either does not exist or the current user does not have permissions on that table.

    OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='mysql', TableName='"database"."dbo"."cust702"'].

    Thanks all!

  • November 8, 2007 at 4:47 pm

    #749674

    You've already set up the linked server right? From the error message, it looks like you have but I thought I'd check. If so, then this looks like a permissions problem. Check and make sure that the user that you set up with the linked server has select permissions on the table.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 9, 2007 at 12:12 am

    #749735

    I don't know for sure... but I think it's highly unlikely that MySQL will have the same nameing structure convention... never mind a user called "dbo".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 9, 2007 at 7:55 am

    #749855

    I'm not using the product "MYSQL" ... I just happened to name my server that. I'm using MSSQL 2000. Sorry for the misleading server name.

  • November 9, 2007 at 2:42 pm

    #749997

    For remote calls ALWAYS use quoted identifiers.

    And it must be double quotes, not square brackets. Because of the drivers implementation.

    select * from "mysql"."database"."dbo"."cust702"

    And don't overuse remote calls.

    They are extremely bad for performance.

    _____________
    Code for TallyGenerator

  • November 9, 2007 at 4:53 pm

    #750013

    SQLcommando (11/9/2007)

    I'm not using the product "MYSQL" ... I just happened to name my server that. I'm using MSSQL 2000. Sorry for the misleading server name.

    Heh... that's probably pretty good for job security, too! 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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