Post reply

Linked Server not queryable

  • June 28, 2007 at 3:22 pm

    #372671

    Hello Everyone,

    I'm having trouble querying a linked IBM UniVerse database from my SQL box.

    When I issue the command:

    SELECT * FROM OpenQuery(SAGITTA, 'SELECT * FROM STAFF')

    I get the data returned properly.  However, if I issue the following command:

     

    SELECT * FROM SAGITTA...STAFF

    I get the following error:

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

    Invalid schema or catalog specified for provider 'MSDASQL'.

    OLE DB error trace [Non-interface error:  Invalid schema or catalog specified for the provider.].

    Has anybody ever seen this before?  If you have any information, it would be greatly appreciated.

    Thanks,

     

    Bob

  • June 28, 2007 at 5:25 pm

    #716028

    Yes, in Oracle and DB2. Try "SELECT * FROM SAGITTA..<SCHEMANAME>.STAFF"

  • June 28, 2007 at 5:35 pm

    #716031

    Kory is spot on... and, (just a reminder)... all of the objects on the Oracle side must be in upper case.

    --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)

  • June 29, 2007 at 6:39 am

    #716146

    This is quite interesting.  After some tinkering the following syntax now works:

    SELECT * FROM SAGITTA...STAFF

    The only change that I can think I made was to create the System DSN on my local machine.    The System DSN being referenced was already created on the server.  I don't know why this would affect anything.  Everything else is the same.

    The odd thing now is when I'm in Enterprise Manager and I expand my "linked servers" and then click on "Tables" under my linked server I get the following error.

    **********************************************

    Error 0: SqlDumpExceptionHandler: Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

    **********************************************

    Not that it is too big of a deal, I'm just confused as to why this worked prior, and my code did not, but now my code works and this does not.....

  • July 4, 2007 at 1:45 am

    #717072

    The four part syntax is only supported for SQL Server linked server and not for other providers. You have to use the 2 part conventions for others. Regarding the EM error can you check the sql server error log at that time and post us with the same.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

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

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