Can I retrieve a result set from Oracle and then incorporate that result set into my main SQL Server Stored Procedure?

So I have a chunk of data that only resides in Oracle. So I need to capture that information from Oracle. Now before you get over zealous, I did try with an OPENQUERY and it took FOREVER! And I don't know why the OPENQUERY took FOREVER but if I run the same query directly against Oracle it runs very quickly...like 20 seconds.

So now I'm wondering...can I build a dataset in my SSRS Report that uses an Oracle Data source and an Oracle Stored Procedure in its Dataset that I'll create to aggregate this subset of data and then utilize its result set back in my main reporting Dataset that will utilize SQL Server? And how can I do that? Can I make my main Dataset reference, say, a #TemporaryTable that is created from my Oracle Dataset in its

I'll continue to Google a few things as I await your review and hopefully a reply.

Thanks in advance for your help.

April 29th, 2015 9:48am

Hi ITBobbyP,

According to your description you want to use data from a Oracle data source into a DataSet which retrieving data from SQL Server. Right?

In Reporting Services, we can have multiple data sources in one project pointing to different database. And we can use separated dataset to retrieve data from different data source. However, it's not supported to combine the two datasets together directly. We can only use Lookup(), LookupSet() function to combine fields from different dataset into one tablix when there are common columns between two datasets. This is the only way to make tow result sets together in SSRS.

Reference:
Lookup Function (Report Builder and SSRS)
LookupSet Function (Report Builder and SSRS)

Best Regards, 

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 3:37am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics