none
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "OraOLEDB.Oracle" for linked server "LINKSERVER-NAME". The provider supports the interface, but returns a failure code when it is used. RRS feed

  • Question

  • My Link Server works fine in SQL Server 2008 R2, but not in SQL Server 2014. I get the message below in SSMS.

    I am connecting to an Oracle DB from SQL Server 2014 database.

    1. SELECT * FROM OPENQUERY(PRIMAVERA_LINK, 'SELECT count(*) from SCHEMA.TABLENAME' ) - works fine.
    2. I can successfully run a query from SQLPLUS.
    3. When I test the Linked Server connection is works fine.
    4. When I expand the tables I get the error below.

    Any help would be appreciated. I have been researching this issue for days.

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "OraOLEDB.Oracle" for linked server "PRIMAVERA_LINK". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.6024&EvtSrc=MSSQLServer&EvtID=7311&LinkId=20476
    ------------------------------
    Suzanne O'Connor

    • Moved by ArthurZ Friday, March 1, 2019 8:34 PM Does not qualify for Integration Servcies section of the forums
    Friday, March 1, 2019 7:38 PM

All replies

  • Hi Suzanne-Perspecta,

     

    From your description, I knew that you got the error when expanding the "Tables".

     

    Firstly, please check the Security configuration for the linked server. How did you map your logins to Oracle. Please check these accounts' permissions to make sure that you can access to these tables successfully.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Abbottee Wednesday, March 6, 2019 1:45 AM
    Monday, March 4, 2019 8:07 AM
  • Our connection is through Linked Server which is the Oracle User Id and password. These items are embedded in that Link. It works in SQL Server 2008 R2 but not in SQL Server 2014.

    Any other ideas?

    Thanks for your help!

    Regards,

    Suzanne


    Suzanne O'Connor


    Wednesday, March 6, 2019 5:30 PM
  • Did you change another driver to connect?
    Sunday, March 10, 2019 3:26 PM