Multiple Data Sources for 1 Dataset?

I have to create a join between tables on SQL Server & a table/file on an
AS400(DB2).
I can create the 2 different data sources just fine, but I cannot figure out
how to join the data into 1 dataset, since you can only use 1 data source.
Has anyone been able to do this? Help!Hod did you go with this question Lori?
I have the same problem.
Thanks
Todd
"Lori" wrote:
> I have to create a join between tables on SQL Server & a table/file on an
> AS400(DB2).
> I can create the 2 different data sources just fine, but I cannot figure out
> how to join the data into 1 dataset, since you can only use 1 data source.
> Has anyone been able to do this? Help!|||I ended up having to create a linked server (on SQL Server) to connect to the
AS/400. I then used a SP to do the query statement. Let me know if you can
find any better way. The linked server does work, it's just not pretty. :-)
"Tango" wrote:
> Hod did you go with this question Lori?
> I have the same problem.
> Thanks
> Todd
> "Lori" wrote:
> > I have to create a join between tables on SQL Server & a table/file on an
> > AS400(DB2).
> > I can create the 2 different data sources just fine, but I cannot figure out
> > how to join the data into 1 dataset, since you can only use 1 data source.
> > Has anyone been able to do this? Help!|||A painfully learned suggestion. Even though with 4 part naming you can do a
heterogenous join, don't. It will pull on the data from the tables locally
to do the join. Use openquery, bring the appropriate data into a temp table
and then join with the temp table. If you have a query with a join (regular
join, not heterogenous) or you have a parameter then even with all the
tables in the query going against the same database it is likely to bring
over all the data locally to process the result. Hence my strong suggestion
to stay away from 4 part naming and use openquery (even though a pain)
instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lori" <Lori@.discussions.microsoft.com> wrote in message
news:39D2A369-C0EC-44E1-B5AB-48A78DA74DB9@.microsoft.com...
>I ended up having to create a linked server (on SQL Server) to connect to
>the
> AS/400. I then used a SP to do the query statement. Let me know if you
> can
> find any better way. The linked server does work, it's just not pretty.
> :-)
> "Tango" wrote:
>> Hod did you go with this question Lori?
>> I have the same problem.
>> Thanks
>> Todd
>> "Lori" wrote:
>> > I have to create a join between tables on SQL Server & a table/file on
>> > an
>> > AS400(DB2).
>> > I can create the 2 different data sources just fine, but I cannot
>> > figure out
>> > how to join the data into 1 dataset, since you can only use 1 data
>> > source.
>> > Has anyone been able to do this? Help!