How to solve Publish Reports via Report Builder 3.0 not able to run against the Oracle Data Source.
I had installed the x64 bit Oracle driver on the server, but was unable to get it to work using the Report Builder 3.0
When you open the Report builder and add a new Data Source for Oracle you can choose between the :
.NET Framework Data Provider for Oracle or a regular ODBC or OLE DB connection
As you can see the Oracle Data Source supports only till version 9i.
So if you want to use a more recent version of Oracle, you need to use the ODBC selection.
Anyhow the news here is that Report Builder 3.0 will ONLY recognize the 32bit drivers.
Lessons learned :
RB 3.0 Data Sources = x32 bit
SSRS Data Sources = x64 bit
If you publish reports to a SharePoint library make sure you have both version of the Oracle driver installed on the server side by side.
1. The older Oracle versions have a different Join Types. The ANSI SQL based one, while MS SQL does not have by default.
So you take this in account when write your queries in the report Builder, which gives you an other challenge 🙂
2. Report Builder has a different way to access the Parameter values using a ‘:’ instead of an ‘@’ character in your Query statement
MS SQL = … WHERE DatePart(year,sub.StartDate) = @Year
ORACLE = …. WHERE To_Char( StartDate,’yyyy’) = :Year