Results 1 to 9 of 9

Thread: Linked Server from SQL Server 2005 to Oracle

  1. #1
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201

    Linked Server from SQL Server 2005 to Oracle

    Hi,

    Oracle client is installed and TNSNAME.ORA is available on the SQL Server where I'd like to create linked server.

    The Oracle provider is "Oracle Provider for OLE DB"
    Product name is "OraOLEDB.Oracle"

    After creating the Oracle Linked server, I tried a simple select statement by 4 part full qualified name as below.

    select * from OraDB..Schema.TBName

    I am getting follwoing error message.

    Msg 7311, Level 16, State 2, Line 1
    Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "OraOLEDB.Oracle" for linked server "OraDB". The provider supports the interface, but returns a failure code when it is used.

    But it works for OPENQUERY. It looks for me that in some situation, 4 part full qualifier name doesnt work while openquery works.

    Could someone please advise the reason?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Oracle is case sensitive, so change all schema, table and column names to upper case

    select * from OraDB..SCOTT.TABLE1

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Also I have seen Microsoft Oledb provider for Oracle giving less problem than Oracle's provider

  4. #4
    Join Date
    Jun 2007
    Posts
    41
    Unfortunately it doesn't work that way, you need use EXECUTE like:

    EXEC ('select * from Schema.TBName') AT OraDB;

    The query executed on Oracle side and return result set.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    This is not the right syntax for SQL Server.

    EXEC ('select * from Schema.TBName') AT OraDB

  6. #6
    Join Date
    Jun 2007
    Posts
    41
    Yes it is correct syntax, check Books Online

    Using EXECUTE to query an Oracle database on a linked server

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    Or use openquery.

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Ok, I take it back. I did not realize it is a new syntax in SQL2K5.

  9. #9
    Join Date
    Nov 2007
    Posts
    1
    I ran into similar problem using ASE OLE DB Provider (Sybase). I was using a data source and had quoted identifiers enabled so Provider was sending incorrect syntax for the call to collect table information. Turned off the quoted identifiers and now it works. Does Oracle data source have a similar setting?

Posting Permissions

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