|
|
You're in the
sql server reporting services
group:Pulling data from two different servers
sql server reporting services:
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,
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. [quoted text, click to view] "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 [quoted text, click to view] "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. [quoted text, click to view] "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 [quoted text, click to view] "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 > >> > >> > > > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |