Hello everyone

We have an Oracle server we need to get data from. We managed to configured it as a linked server in SSMS, so now under Server Objects\Linked Servers\, we have a server called Oracle and we can see our data in there.

Now, we need to do reporting with SSRS against that same Oracle server. When I go to my SSRS server, I click on New Data Source. 

Question - can I point this Data Source to the Linked Server from above, or directly to the Oracle server? What are my options, and what is the best practice?

Do I need any special software on the SSRS server, like Oracle Client? What data source should I use, and what is my connection string?

Any help would be appreciated!

As you have already setup linked server,you can set up datasource  in ssrs 

for the  sqlserver which has got linked server ,and when you write the query include the linked server  name ,or you can use the oracle provider when you setup the data source ,the later one i have not used,but the linked server method should work.

...................................................................................................................................................

Please mark this post as Answered,if it helps to solve your issue.

Hi DarkoTeodorovic,

According to your description, you want to create a report with retrieving data from Oracle database.

In Reporting Services, its supported to use Oracle database as datasource. You can create an embedded data source that uses the .NET Framework Managed Provider for Oracle data processing extension or the OLE DB data processing extension that uses the Oracle data provider. For more information, please refer to this article: How to: Retrieve Data from an Oracle Data Source.

If you have set up an linked server for an Oracle database, you can create a stored procedure in any of your databases and query the linked server tables from within that stored procedure. Then you can create a new datasource in SSRS which points to the database where you have created the above stored procedure, call that stored procedure as your command in the dataset. So that the report will return the records from the linked server table. About how to create a linked server for Oracle, you can refer to this article: Connecting SQL Server and Oracle Using Linked Servers.

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu



You can take oracle entry from tnsnames.ora .

You can use openquery also.

Following example of link server.

The linked server name is a descriptive name for the linked server and can be any valid SQL Server name.
the system name of OR-PORT-VORA11G. You can select the OLE DB provider that you want to use from the Provider drop-down list.
 In Figure 6 you can see that I selected the Oracle Provider for OLE DB. 
selected the Microsoft OLE DB Provider for Oracle. The Product name text box accepts 
the OLE DB providers product name. If you dont know this name, you can expand the Server Objects, Linked Server, 
Provider node to see a list of the installed OLE DB providers. T
he product name for the Oracle OLE DB Provider is OraOLEDB. 
In the Data source text box, enter the Oracle service name.

query :- 

       SELECT * FROM \[OR-PORT-VORA11G\]..SCOTT.EMP.  

need to set the property RPC out  = TRUE for that linked server

• Proposed as answer by Dinesh Vishe Tuesday, April 07, 2015 11:51 AM
• Unproposed as answer by Dinesh Vishe Monday, April 20, 2015 7:07 AM
• Edited by Dinesh Vishe Tuesday, April 21, 2015 6:22 AM
• Marked as answer by DarkoTeodorovic 9 hours 38 minutes ago

Thanks everyone, I think we finally solved it!