2

Like the title says, I cannot select the "Oracle Database" on my SSRS site.

enter image description here

  1. Checked the Server Version. Windows Server 2016 Standard with SQL Server 2017 Developer Edition (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f82ea8c1-63b8-4b9b-a0d3-a24a6bad7ac9/oracle-option-isnt-appearing-in-the-dropdown-list-of-connection-type-on-report-server?forum=sqlreportingservices)

  2. Installed ODAC 32 and 64 bits. (https://blogs.msdn.microsoft.com/dataaccesstechnologies/2018/03/26/configure-oracle-data-source-for-sql-server-reporting-services-ssdt-and-report-server/)

  3. Checked rsreportserver.config for Oracle Extension.

<Data>
 <Extension Name="SQL" Type="Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="SQLAZURE" Type="Microsoft.ReportingServices.DataExtensions.SqlAzureConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="SQLPDW" Type="Microsoft.ReportingServices.DataExtensions.SqlDwConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="OLEDB-MD" Type="Microsoft.ReportingServices.DataExtensions.AdoMdConnection,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="SHAREPOINTLIST" Type="Microsoft.ReportingServices.DataExtensions.SharePointList.SPListConnection,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="ORACLE" Type="Microsoft.ReportingServices.DataExtensions.OracleClientConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="ESSBASE" Type="Microsoft.ReportingServices.DataExtensions.Essbase.EssbaseConnection,Microsoft.ReportingServices.DataExtensions.Essbase"/>
 <Extension Name="SAPBW" Type="Microsoft.ReportingServices.DataExtensions.SapBw.SapBwConnection,Microsoft.ReportingServices.DataExtensions.SapBw"/>
 <Extension Name="TERADATA" Type="Microsoft.ReportingServices.DataExtensions.TeradataConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="OLEDB" Type="Microsoft.ReportingServices.DataExtensions.OleDbConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="ODBC" Type="Microsoft.ReportingServices.DataExtensions.OdbcConnectionWrapper,Microsoft.ReportingServices.DataExtensions"/>
 <Extension Name="XML" Type="Microsoft.ReportingServices.DataExtensions.XmlDPConnection,Microsoft.ReportingServices.DataExtensions"/>
</Data>
<SemanticQuery>
 <Extension Name="SQL" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.MSSQL.MSSqlSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
  <Configuration>
   <EnableMathOpCasting>False</EnableMathOpCasting>
  </Configuration>
 </Extension>
 <Extension Name="SQLAZURE" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.MSSQL.MSSqlSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
  <Configuration>
   <EnableMathOpCasting>False</EnableMathOpCasting>
  </Configuration>
 </Extension>
 <Extension Name="SQLPDW" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.MSSQLADW.MSSqlAdwSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
  <Configuration>
   <EnableMathOpCasting>False</EnableMathOpCasting>
  </Configuration>
 </Extension>
 <Extension Name="ORACLE" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Oracle.OraSqlSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
  <Configuration>
   <EnableMathOpCasting>True</EnableMathOpCasting>
   <DisableNO_MERGEInLeftOuters>False</DisableNO_MERGEInLeftOuters>
   <EnableUnistr>False</EnableUnistr>
   <DisableTSTruncation>False</DisableTSTruncation>
  </Configuration>
 </Extension>
 <Extension Name="TERADATA" Type="Microsoft.ReportingServices.SemanticQueryEngine.Sql.Teradata.TdSqlSQCommand,Microsoft.ReportingServices.SemanticQueryEngine">
  <Configuration>
   <EnableMathOpCasting>True</EnableMathOpCasting>
   <ReplaceFunctionName>oREPLACE</ReplaceFunctionName>
  </Configuration>
 </Extension>
 <Extension Name="OLEDB-MD" Type="Microsoft.AnalysisServices.Modeling.QueryExecution.ASSemanticQueryCommand,Microsoft.AnalysisServices.Modeling"/>
</SemanticQuery>
  1. Restarted Reporting Services and IIS

Still not there.

What am I missing?

References:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3acd5b74-6530-42ca-8be1-2ec09c1c34ed/oracle-data-extension-not-registered?forum=sqlreportingservices

https://blogs.msdn.microsoft.com/dataaccesstechnologies/2018/03/26/configure-oracle-data-source-for-sql-server-reporting-services-ssdt-and-report-server/

https://social.msdn.microsoft.com/Forums/en-US/3150913d-f504-420f-acce-559e96556ee7/connection-issues-ssrs-2016-rc3-to-oracle?forum=sqlreportingservices

https://social.msdn.microsoft.com/Forums/en-US/0ff3e598-fdd9-4666-a7f9-d665a3b61338/connecting-to-an-oracle-db-in-ssrs?forum=sqlreportingservices https://learn.microsoft.com/en-us/sql/reporting-services/report-data/oracle-connection-type-ssrs?view=sql-server-2017

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f82ea8c1-63b8-4b9b-a0d3-a24a6bad7ac9/oracle-option-isnt-appearing-in-the-dropdown-list-of-connection-type-on-report-server?forum=sqlreportingservices

https://social.msdn.microsoft.com/Forums/en-US/0c00012f-c423-4cfe-b3a0-434f86823b5e/how-to-avoid-the-data-processing-extension-used-for-this-report-is-not-available-it-has-either?forum=sqlreportingservices

https://learn.microsoft.com/en-us/sql/reporting-services/reporting-services-features-supported-by-the-editions-of-sql-server-2016?view=sql-server-2017

SSRS 2016 Data source won't display Oracle connection type

blfuentes
  • 2,731
  • 5
  • 44
  • 72
  • Did you install ODAC 32 and 64 on the report server as well as your machine? – aduguid Sep 16 '18 at 23:37
  • @aduguid yes, I installed both in both machines. I can develop and use the oracle database without problems in the dev environment but then it raises error when trying to call the report. So I tried to add the data source manually on the server and the option wasn't even there at all – blfuentes Sep 17 '18 at 06:01
  • Did you copy your tnsnames.ora file to the report server? – aduguid Sep 17 '18 at 06:44
  • @aduguid yes, the tnsnames.ora file is also there – blfuentes Sep 17 '18 at 06:45
  • 1
    Have you tried using the `OLE DB` connection type? Connection String = `OraOLEDB.Oracle.1;Data` – aduguid Sep 17 '18 at 06:51

1 Answers1

0

Install ODAC, use OLE DB and add Provider=OraOLEDB.Oracle.1; to your connection string.

With SSRS 2019 and Oracle 12.1, it does not exactly add oracle connection type, but it was enough for me to connect to my database.

Antoine L
  • 431
  • 4
  • 6