February 23, 2015 at 2:45 am
Hi,
Is it possible to create a dataset in reporting services based on 2 databases if they sit on different servers ? I saw something about linked servers but don't know how to link 2 servers.
Could someone please advice if we can join 2 servers and create a dataset based on that ?
Thanks.
February 23, 2015 at 2:13 pm
Hi PWalter,
Yes you can create a query based on two different servers, here's some info on how to set it up:
https://msdn.microsoft.com/en-GB/library/ff772782.aspx
Your query would end up looking something like this:
SELECT a.Column1, a.Column2, b.Column1, b.Column2
FROM ServerA.DatabaseA.SchemaA.TableA a
INNER JOIN ServerB.DatabaseB.SchemaB.TableB b ON a.Column1 = b.Column1
WHERE ......
The other thing you could do is create two data sets in your report, one from Server A and one from Server B. Then you could use a LOOKUP to link the two sets together. This may be your only option if your DBAs won't let you link servers. Here's some info:
February 24, 2015 at 4:23 am
Jim Mackenzie (2/23/2015)
Hi PWalter,Yes you can create a query based on two different servers, here's some info on how to set it up:
https://msdn.microsoft.com/en-GB/library/ff772782.aspx
Your query would end up looking something like this:
SELECT a.Column1, a.Column2, b.Column1, b.Column2
FROM ServerA.DatabaseA.SchemaA.TableA a
INNER JOIN ServerB.DatabaseB.SchemaB.TableB b ON a.Column1 = b.Column1
WHERE ......
The other thing you could do is create two data sets in your report, one from Server A and one from Server B. Then you could use a LOOKUP to link the two sets together. This may be your only option if your DBAs won't let you link servers. Here's some info:
Thanks for your advice Jim.
I guess I would have to go with the lookup option as linked servers are not allowed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply