Pulling data from two different servers

Hi,
I have some data I'd like to retrieve and run a report from, but the data is
one 2 different servers. I don't know how to set this up in report manager
or create a query that says something like
select server.database.field, differentserver.database.field etc.
Any help would be greatly appreciated.
Thanks
JillJill,
The solution to your question lies solely within your SQL query and
Reporting Services will not affect how you solve this problem.
The easiest way to perform this task is to use a four-part query that
specifies the tables you want to pull from your remote server.
The format for the reference to the table is
"Servername.databasename.owner.table"
For example
select *
from myserver1.pubs.dbo.authors as A
join myserver2.pubs.dbo.titleauthor as B
on A.au_id = b.au_id
To perform this type of query you'll need to create a linked server. For
instance, if your connection is "myserver1" you need to create a linked
server on the myserver1 server to myserver2. If you Google "Linked Server
SQL Security" you will find articles instructing you how to do so. While
explaining the query is fairly straight forward, explaining Linked Server
security and permissions is best left up to people who have already written
about it.
Hope this helps.
"Jill" <Jill@.discussions.microsoft.com> wrote in message
news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> Hi,
> I have some data I'd like to retrieve and run a report from, but the data
> is
> one 2 different servers. I don't know how to set this up in report
> manager
> or create a query that says something like
> select server.database.field, differentserver.database.field etc.
> Any help would be greatly appreciated.
> Thanks
> Jill|||If you tables are large you need to be very careful with how you use the 4
part naming method of using linked servers. You can end up where an
incredible amount of data is brought locally to do the join. You are better
off to do this in two parts. First use openquery instead of 4 part naming.
Have two temp tables that two separate queries put their results into. Then
join the two temp tables together. The performance will be much much better.
If your tables are of any size I strongly recommend this approach (speaking
from painful learning experience).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Jill,
> The solution to your question lies solely within your SQL query and
> Reporting Services will not affect how you solve this problem.
> The easiest way to perform this task is to use a four-part query that
> specifies the tables you want to pull from your remote server.
> The format for the reference to the table is
> "Servername.databasename.owner.table"
> For example
> select *
> from myserver1.pubs.dbo.authors as A
> join myserver2.pubs.dbo.titleauthor as B
> on A.au_id = b.au_id
> To perform this type of query you'll need to create a linked server. For
> instance, if your connection is "myserver1" you need to create a linked
> server on the myserver1 server to myserver2. If you Google "Linked Server
> SQL Security" you will find articles instructing you how to do so. While
> explaining the query is fairly straight forward, explaining Linked Server
> security and permissions is best left up to people who have already
written
> about it.
> Hope this helps.
>
> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> > Hi,
> > I have some data I'd like to retrieve and run a report from, but the
data
> > is
> > one 2 different servers. I don't know how to set this up in report
> > manager
> > or create a query that says something like
> > select server.database.field, differentserver.database.field etc.
> > Any help would be greatly appreciated.
> >
> > Thanks
> > Jill
>|||While not as robust as the other solutions, you could also use a subreport
to accomplish the same thing.
Mike G.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uZezRATFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> If you tables are large you need to be very careful with how you use the 4
> part naming method of using linked servers. You can end up where an
> incredible amount of data is brought locally to do the join. You are
> better
> off to do this in two parts. First use openquery instead of 4 part naming.
> Have two temp tables that two separate queries put their results into.
> Then
> join the two temp tables together. The performance will be much much
> better.
> If your tables are of any size I strongly recommend this approach
> (speaking
> from painful learning experience).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
>> Jill,
>> The solution to your question lies solely within your SQL query and
>> Reporting Services will not affect how you solve this problem.
>> The easiest way to perform this task is to use a four-part query that
>> specifies the tables you want to pull from your remote server.
>> The format for the reference to the table is
>> "Servername.databasename.owner.table"
>> For example
>> select *
>> from myserver1.pubs.dbo.authors as A
>> join myserver2.pubs.dbo.titleauthor as B
>> on A.au_id = b.au_id
>> To perform this type of query you'll need to create a linked server. For
>> instance, if your connection is "myserver1" you need to create a linked
>> server on the myserver1 server to myserver2. If you Google "Linked Server
>> SQL Security" you will find articles instructing you how to do so. While
>> explaining the query is fairly straight forward, explaining Linked Server
>> security and permissions is best left up to people who have already
> written
>> about it.
>> Hope this helps.
>>
>> "Jill" <Jill@.discussions.microsoft.com> wrote in message
>> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
>> > Hi,
>> > I have some data I'd like to retrieve and run a report from, but the
> data
>> > is
>> > one 2 different servers. I don't know how to set this up in report
>> > manager
>> > or create a query that says something like
>> > select server.database.field, differentserver.database.field etc.
>> > Any help would be greatly appreciated.
>> >
>> > Thanks
>> > Jill
>>
>|||True. I should have mentioned this. If it is master/detail type of
relationship the subreports are the way to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike G." <theNOSPAMjunkbox@.comcast.net> wrote in message
news:OMxYhDTFFHA.1968@.tk2msftngp13.phx.gbl...
> While not as robust as the other solutions, you could also use a subreport
> to accomplish the same thing.
> Mike G.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uZezRATFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> > If you tables are large you need to be very careful with how you use the
4
> > part naming method of using linked servers. You can end up where an
> > incredible amount of data is brought locally to do the join. You are
> > better
> > off to do this in two parts. First use openquery instead of 4 part
naming.
> > Have two temp tables that two separate queries put their results into.
> > Then
> > join the two temp tables together. The performance will be much much
> > better.
> > If your tables are of any size I strongly recommend this approach
> > (speaking
> > from painful learning experience).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> > news:%23hmoatSFFHA.3732@.TK2MSFTNGP14.phx.gbl...
> >> Jill,
> >>
> >> The solution to your question lies solely within your SQL query and
> >> Reporting Services will not affect how you solve this problem.
> >>
> >> The easiest way to perform this task is to use a four-part query that
> >> specifies the tables you want to pull from your remote server.
> >>
> >> The format for the reference to the table is
> >> "Servername.databasename.owner.table"
> >>
> >> For example
> >>
> >> select *
> >> from myserver1.pubs.dbo.authors as A
> >> join myserver2.pubs.dbo.titleauthor as B
> >> on A.au_id = b.au_id
> >>
> >> To perform this type of query you'll need to create a linked server.
For
> >> instance, if your connection is "myserver1" you need to create a linked
> >> server on the myserver1 server to myserver2. If you Google "Linked
Server
> >> SQL Security" you will find articles instructing you how to do so.
While
> >> explaining the query is fairly straight forward, explaining Linked
Server
> >> security and permissions is best left up to people who have already
> > written
> >> about it.
> >>
> >> Hope this helps.
> >>
> >>
> >> "Jill" <Jill@.discussions.microsoft.com> wrote in message
> >> news:B37A4B57-F8A9-44DC-B43F-63F63F1902F3@.microsoft.com...
> >> > Hi,
> >> > I have some data I'd like to retrieve and run a report from, but the
> > data
> >> > is
> >> > one 2 different servers. I don't know how to set this up in report
> >> > manager
> >> > or create a query that says something like
> >> > select server.database.field, differentserver.database.field etc.
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks
> >> > Jill
> >>
> >>
> >
> >
>