Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hassanmoussa
Frequent Visitor

Can't connect to Oracle Database from Microsoft Report Builder

Hi,

 

I have installed the 32 bit of ODAC on my laptop. The installer gave me the option for machine wide level but not GAC something (The option was not there. It used to be there). I can connect Power BI desktop to the Oracle Database but not report builder.

 

It is giving me the below issue:

 

"An attempt has been made to use a data extension 'ORACLE' that is either not registered for this report server or is not supported in this edition of Reporting Services."

 

I think I have 64 bit or 32 bit on server. I am not sure, but it is working fine for Power BI files on server. I also have Power BI Deskop x64 and it is working fine.

 

I am using the below connection string:

Data Source=Service_Name;User Id=user;Password=password;Integrated Security=no;

 

Could you please help?

1 ACCEPTED SOLUTION
Hassanmoussa
Frequent Visitor

OK Here is an update. First, many thanks to @v-deddai1-msft

 

Here are the steps I followed one by one to solve my problem. Please Read them first, then execute. Otherwise you will get lost and get into trouble.

 

1- Delete any reference to oracle packages in machine.config:

See this article to find machine.config

.net - Where Is Machine.Config? - Stack Overflow

- Please becareful not delete parent section of the reference. e.g. see below:

<system.data>
<DbProviderFactories>
"Some Add elements"
</DbProviderFactories>
</system.data>

- So you need to delete what's inside DbProviderFactories tag, not the whole tag.

 

2- Go the location of the oracle installation, and delete eveyerthing. Usually it is something like:

C:\app\client\product\12.2.0

-So what I did is I deleted the whole app folder.

 

3- Go to environment variable and delete the references to the oracle drivers. Below screenshot should say all:

Hassanmoussa_2-1623054729375.png

 

4- Go to this article below. For your laptop / work computer, install 32 bit and 64 but execute the command lines for managed 32 and unmanged 64. I had report builder (32bit uses managed) and Power BI Desktop (64 bit uses Unmanged). 

Oracle Connection Type (SSRS & Power BI Report Server) - SQL Server Reporting Services (SSRS) | Micr...

-Please don't use codes you find on the forum. Only use codes from the article.

 

5- On the server just install the 64 bit. But execute commands for both managed and unmanaged. The article above will explain it all.

 

6- If you have .Net applications in Visual Studio, please make sure they use the same version of oracle packages from Nuget that you installed.

 

Few Final notes:

A- Try to have the same version on server and on your development laptop. This will be very important if you have .Net apps. Otherwise your apps won't run if they refrence a higher version and on the server you have a lower version. or Atleast that's my experience.

 

B- I used the oracle (.exe) installer for 64 bit and 32 bit. Please run it as Admin otherwise it will throw an error. I never needed to use XCopy and I don't think you need that.

 

 

View solution in original post

7 REPLIES 7
petergroft
New Member

Connect Database Using The Direct Connection Method

This method works with all the versions of Oracle Forms builder above 6i. The steps are as follows:

  1. In Oracle Forms builder, click on the File menu and then click Connect option.
  2. Then in the Connect window, provide the username in the Username field.
  3. Provide a password in the Password field.
  4. In the Database field, provide the information using the format IP-Address:Port/OracleSID. For example, 123.456.78.9:1521/ORCL.
  5. Then click on the connect button to connect the database.

This May Work,

Peter

Hassanmoussa
Frequent Visitor

OK Here is an update. First, many thanks to @v-deddai1-msft

 

Here are the steps I followed one by one to solve my problem. Please Read them first, then execute. Otherwise you will get lost and get into trouble.

 

1- Delete any reference to oracle packages in machine.config:

See this article to find machine.config

.net - Where Is Machine.Config? - Stack Overflow

- Please becareful not delete parent section of the reference. e.g. see below:

<system.data>
<DbProviderFactories>
"Some Add elements"
</DbProviderFactories>
</system.data>

- So you need to delete what's inside DbProviderFactories tag, not the whole tag.

 

2- Go the location of the oracle installation, and delete eveyerthing. Usually it is something like:

C:\app\client\product\12.2.0

-So what I did is I deleted the whole app folder.

 

3- Go to environment variable and delete the references to the oracle drivers. Below screenshot should say all:

Hassanmoussa_2-1623054729375.png

 

4- Go to this article below. For your laptop / work computer, install 32 bit and 64 but execute the command lines for managed 32 and unmanged 64. I had report builder (32bit uses managed) and Power BI Desktop (64 bit uses Unmanged). 

Oracle Connection Type (SSRS & Power BI Report Server) - SQL Server Reporting Services (SSRS) | Micr...

-Please don't use codes you find on the forum. Only use codes from the article.

 

5- On the server just install the 64 bit. But execute commands for both managed and unmanaged. The article above will explain it all.

 

6- If you have .Net applications in Visual Studio, please make sure they use the same version of oracle packages from Nuget that you installed.

 

Few Final notes:

A- Try to have the same version on server and on your development laptop. This will be very important if you have .Net apps. Otherwise your apps won't run if they refrence a higher version and on the server you have a lower version. or Atleast that's my experience.

 

B- I used the oracle (.exe) installer for 64 bit and 32 bit. Please run it as Admin otherwise it will throw an error. I never needed to use XCopy and I don't think you need that.

 

 

This is a lifesaver. This whole SSRS-Oracle connection is a nightmare, and you have solved about 6 months of frustration. Thank you so much!

Anonymous
Not applicable

Hi @Hassanmoussa ,

I had faced same issue on Report builder, then I had tried to connect to Oracle database through ' ODBC ' type ( 32bit ) and it went fine. You can try this way ...

v-deddai1-msft
Community Support
Community Support

Hi @Hassanmoussa ,

 

Trying to un-install and re-install ODAC 12.2. And when you run the cmd in https://docs.microsoft.com/en-us/sql/reporting-services/report-data/oracle-connection-type-ssrs?view... . Please use run as administrator.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

How do I uninstall the ODAC exactly? There is no uninstall for that. Should I simply delete the files? Could you please be more specific and share an article please?

Hassanmoussa
Frequent Visitor

Update:

I have run both cmd lines on both server and my machine. They were in the below article. On both server and my machine I am using version 12.2

 

https://docs.microsoft.com/en-us/sql/reporting-services/report-data/oracle-connection-type-ssrs?view...

 

Still same issue.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.