Query Against Multiple Databases on different Database Serve

building reports desktopintelligence
1

I was wanting to know if anyone knew if it was possible to run a free hand SQL statement in Business Objects that could join two tables on different servers in different databases. For Example,

Select x.column, y.column
From Server1.Owner.Database.Table x, Server2.Owner.Database2.Table y
Where Server1.Owner.Database.Table.Column = Server2.Owner.Database2.Table.Column

I am thinking there is a limitation due to having to select a connection in the free hand SQL window. If it is not possible, does anyone have any suggestions how to get this type of query into Business Objects.

wbuente (BOB member since 2004-06-21)

2

Which DBMS are you using (Oracle, MS SQL Server etc.)?
If all is in Oracle you can use the DBlink function for example.

Andreas :de: (BOB member since 2002-06-20)

3

I am using MS SQL Server 2000. Thanks!

wbuente (BOB member since 2004-06-21)

4

Not unless you are doing something with some sort of database link so that it all looks like 1 database.

I don’t know if SQL server does anything like this.

Check out www.centerboard.com. They sell a technology that will handle this.

Steve Krandel :us: (BOB member since 2002-06-25)

5

I think - in SQL Server - that you can address two databases on the same server. But in your example, you address two different servers. So I think that’s going to be your issue.

Dave Rathbun :us: (BOB member since 2002-06-06)

6

That is what I thought. Thank You for you assistance!

wbuente (BOB member since 2004-06-21)

7

Have a look at the procedure sp_addlinkedserver. You can then query Oracle and SQL tables in the same query. AS400 is a bit messier, but Oracle and SQL Server work fine together. Performance has been OK for what I have used them for.