SSRS shared data source : error ORA-03134

This error came up setting up a shared Data Source in SharePoint ?!

ORA-03134: Connections to this server version are no longer supported.

For a mix of older versions of Oracle  databases. You need to pick the most appropriate driver.

Solution : Install a lower version of the Instant Client Libr. (just downgrade to the lowest level of Database version)

The good news:

It is possible to access an Oracle 8 Server with Oracle Instant Client 10 (see Metalink Note 207303.1).
The bad news: There is no Oracle Instant Client version 9. The product was introduced with Oracle 10g.

Connections to Oracle 9.0.1 databases are not supported.
Connecting with a 10.2 Instant Client to server version 9.2.0.4 or higher is supported.

or avoid the problem

Don’t use a shared Data Source but a Report Data Source

Report Builder 3.0 – Oracle Data Source Issue

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.

REMARKS:

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