Accessing two Database

Dose anybody know if it is possible to access a DB table from SQL Srvr, and
another table from Oracle in the same report?
Is this even possible and if yes, how?
Than you,
TiganIts possible and quite easy. You have to create a different datasource
and a different dataset. Go to the Data Tab in report designer, add
new dataset - specify the new data source - or if the data source is
same, just mention the new query and see if the tuples are retrived.
Now come back to design tab, you can select the dataset from the left
side toolbar and have the fields frm the new dataset linked to a table
/ textbox etc.|||Thank you Aravind,
I am aware of this solution. This would allow me to have different fields in
the report pointing to different data source. However this is not the problem
I am trying to solve. Specifically, the problem is: I have a Oracle DB that
has all the item_infoormation (e.g item name, S/N, description, etc.), and
the SQL DB has all the item_usage data (a log how how many time the is used).
Now, I like to create a usage report that access the SQL to get the usage
data and use the S/N to link to the Oracle DB and get the item_name,
description, etc.)
So, I need to create a view or something that can quesry both DB and report
a consolidated dataset with both usage data and item information. I do not
think your proposed solution will solve this.
What do you think?
Tigan.
"Aravind" wrote:
> Its possible and quite easy. You have to create a different datasource
> and a different dataset. Go to the Data Tab in report designer, add
> new dataset - specify the new data source - or if the data source is
> same, just mention the new query and see if the tuples are retrived.
> Now come back to design tab, you can select the dataset from the left
> side toolbar and have the fields frm the new dataset linked to a table
> / textbox etc.
>|||In this case my suggestion is to use a subreport. The subreport should have
a parameter that ties the data together (essentially doing the join for
you). You don't want to do a join although this is possible with linked
tables in SQL Server your performance would be horrible so I strongly
recommend against using linked servers to solve this problem). So, one
report has the SQL data, another report (which will act as the sub report)
has the Oracle data. Test the report separately and make sure works as you
want. Then drag and drop the report which will act as the subreport onto the
main report. Then do a right mouse click on the subreport and setup the
parameters.
Note that joining two datasets is not possible so your choice is using a
subreport or doing a heterogeous join using linked tables in SQL Server and
believe me, if your tables are any size whatsoever you do not want to be
doing a heterogenous join.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"tigansan" <tigansan@.discussions.microsoft.com> wrote in message
news:E245BAFE-16D3-4F77-9053-9AEBA116F09D@.microsoft.com...
> Thank you Aravind,
> I am aware of this solution. This would allow me to have different fields
> in
> the report pointing to different data source. However this is not the
> problem
> I am trying to solve. Specifically, the problem is: I have a Oracle DB
> that
> has all the item_infoormation (e.g item name, S/N, description, etc.), and
> the SQL DB has all the item_usage data (a log how how many time the is
> used).
> Now, I like to create a usage report that access the SQL to get the usage
> data and use the S/N to link to the Oracle DB and get the item_name,
> description, etc.)
> So, I need to create a view or something that can quesry both DB and
> report
> a consolidated dataset with both usage data and item information. I do
> not
> think your proposed solution will solve this.
> What do you think?
> Tigan.
>
> "Aravind" wrote:
>> Its possible and quite easy. You have to create a different datasource
>> and a different dataset. Go to the Data Tab in report designer, add
>> new dataset - specify the new data source - or if the data source is
>> same, just mention the new query and see if the tuples are retrived.
>> Now come back to design tab, you can select the dataset from the left
>> side toolbar and have the fields frm the new dataset linked to a table
>> / textbox etc.
>>|||Thank you Bruce,
I will give it a try using subreports.
Tigan
"Bruce L-C [MVP]" wrote:
> In this case my suggestion is to use a subreport. The subreport should have
> a parameter that ties the data together (essentially doing the join for
> you). You don't want to do a join although this is possible with linked
> tables in SQL Server your performance would be horrible so I strongly
> recommend against using linked servers to solve this problem). So, one
> report has the SQL data, another report (which will act as the sub report)
> has the Oracle data. Test the report separately and make sure works as you
> want. Then drag and drop the report which will act as the subreport onto the
> main report. Then do a right mouse click on the subreport and setup the
> parameters.
> Note that joining two datasets is not possible so your choice is using a
> subreport or doing a heterogeous join using linked tables in SQL Server and
> believe me, if your tables are any size whatsoever you do not want to be
> doing a heterogenous join.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "tigansan" <tigansan@.discussions.microsoft.com> wrote in message
> news:E245BAFE-16D3-4F77-9053-9AEBA116F09D@.microsoft.com...
> > Thank you Aravind,
> > I am aware of this solution. This would allow me to have different fields
> > in
> > the report pointing to different data source. However this is not the
> > problem
> > I am trying to solve. Specifically, the problem is: I have a Oracle DB
> > that
> > has all the item_infoormation (e.g item name, S/N, description, etc.), and
> > the SQL DB has all the item_usage data (a log how how many time the is
> > used).
> > Now, I like to create a usage report that access the SQL to get the usage
> > data and use the S/N to link to the Oracle DB and get the item_name,
> > description, etc.)
> >
> > So, I need to create a view or something that can quesry both DB and
> > report
> > a consolidated dataset with both usage data and item information. I do
> > not
> > think your proposed solution will solve this.
> >
> > What do you think?
> > Tigan.
> >
> >
> > "Aravind" wrote:
> >
> >> Its possible and quite easy. You have to create a different datasource
> >> and a different dataset. Go to the Data Tab in report designer, add
> >> new dataset - specify the new data source - or if the data source is
> >> same, just mention the new query and see if the tuples are retrived.
> >> Now come back to design tab, you can select the dataset from the left
> >> side toolbar and have the fields frm the new dataset linked to a table
> >> / textbox etc.
> >>
> >>
>
>