report builder: creating oracle data source works but the data set cannot refresh fields

Gordian Code 1 Reputation point
2022-04-26T14:04:40.907+00:00

Hello,
I have migrated a reporting server to another machine and all reports accessing an oracle data base are working. However, when I want to edit a data set of a report in the report builder on the new server machine I get the error
"The selected data extension ORACLE is not installed or cannot be loaded. Verify that the selected data extension is installed on the client for local reports and on the report server for published reports."

I have removed the existing data source and recreated it. A click on 'Test Connection' delivers "Connection created successfully". After applying that newly created data source to a data set I am getting again the above error. Even a completely new data set using that data source throws the same error.

I repeated the procedure by using the option 'Use a connection embedded in my report'. Same result: Test connection is successful, applying it to a data set throws that error.

I am pretty sure that there is no problem with the ODAC installation, otherwise I would expect that the former reports do not work. I could even remove the old data source, create a new embedded one with the same name and the report works, but when I want to edit the data set I get "The selected data extension ORACLE is not installed or cannot be loaded. Verify that the selected data extension is installed on the client for local reports and on the report server for published reports". Thanks for help!

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,799 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,566 Reputation points
    2022-04-27T03:55:48.493+00:00

    Hi @Gordian Code ,
    Actually, this error "The selected data extension ORACLE is not installed or cannot be loaded. Verify that the selected data extension is installed on the client for local reports and on the report server for published reports." usually occurs in the following cases:

    • You didn't install Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio and ODP.NET.
    • You have already installed an incorrect ODAC version that doesn't match the correct Oracle Database Version.

    Install the appropriate version for "ODAC, ODP.NET and Oracle Developer Tools" based on your Oracle database server.

    • ODAC 18.3 (Oracle 11g Release 2 or later)
    • ODAC 12.2 (Oracle 10g Release 2 or later)
    • ODAC 11.2 (Oracle 9i Release 2 or later)

    See more: SSRS: The selected data extension ORACLE is not installed or cannot be loaded.

    For cases where dataset fields cannot be refreshed, you can run the query directly in the Oracle database to check that the query is correct. And try refrefreshing the dataset fields by following this link: https://www.sqlchick.com/entries/2012/2/6/workaround-for-could-not-update-a-list-of-fields-for-the -que.html

    Best Regards,
    Joy

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments