Interview Question in SQL Server Reporting Services (SSRS)
Interview Question :: How do I join two tables from two separate database in SQL Server 2005 Reporting Services
1) Just like you do it in any query.
select tbl1.col1, db2..tbl2.col2
from tbl1 join db2..tbl2
on tbl1.id = db2..tbl2.id
2) Or you can use OPENROWSET or one of its sister functions.
3) Or you can do it in code elsewhere and hook that code up to a web service and set your dataset up to pull from that web service.
4) Or you might be able to do it using custom assemblies.
5) Or you can get really tricky with report parameters and multiple datasets.
The downsides:
1) You have to know the name of the other database (and possibly the table owner). And the database has to be on the same server. And you won't get a good error message if there is a problem.
2) You have to store the entire connection string in your query.
3) You have the overhead of the transport layer and the added architectural complexity or the web service.
4) Haven't researched this, but it could get complex.
5) It's one big hack, complexity will grow exponentially.
It could all be solved if MS just added the ability to modify the SQL at run-time.
It seems that the best method is to re-architect a few things on the backend or in the report's design and avoid the issue of having to join the two databases in the first place.
Loading ...