How to combine result sets from different DB using Business View

[DarthGAD]

Crystal Reports XI with BusinessObjects Enterprise XI
BusinessViews
MS SQL 2000 Databases
I have two database servers SERVER1 AND SERVER2.
SERVER1 stores realtime data in a table.
SERVER2 stores historical data and has a view to access the data.
Datasets are compatible (Same fields)

SAMPLE:
SERVER1 DB1
Table: Devices
Columns: DeviceID, DeviceName, DeviceStatus, DeviceLastUpdate

SERVER2 DB2
View: Devices
Columns: DeviceID, DeviceName, DeviceStatus, DeviceLastUpdate

I need to combine the result sets into one for reporting purposes. If both the table and view resided on the same server I would use a union. We created a SQL Server link to be able to use a union but this did not work because of a permissions issue that we can not resolve (It would break one app). I want to see if there is a way I can do this with a Business View.

 

[satinsilhouette]

Yes, set up both database connections and then add both connections into your database foundation and build the elements from the foundation and publish up through a Business View.

Thanks so much!
satinsilhouette

 

[DarthGAD]

satinsilhouette:

The question is how to do it. Remember that I am trying to combine both tables into one result set. Something I would typically do with a untion such as:

Select DeviceName
from SERVER1.DB1

union

Select DeviceName
from SERVER2.DB2

So if...
SERVER1.DB1.DeviceName had the following data:
Alpha
Bravo
Charlie
Delta

and..
SERVER2.DB2.DeviceName had the following data:
Echo
Foxtrot
Gulf
Hotel

then the result set would be...

Alpha
Bravo
Charlie
Delta
Echo
Foxtrot
Gulf
Hotel

I don't see an obvious way of doing this so a more specific example would be greatly appreciated.

 

[Gwired]

I don't believe Crystal has the ability to Union from two different databases. So you would have to create a Union using a database engine that has that power.

You could use Access, set up two ODBC links and then build a Union Query then have Crystal point to that Query.

Or for even more fun, if you have SQL Server you can use the OpenDatabase command to Open a table in another database. Here is an example of a query that I wrote to Access an access database from SQL

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source = \\SERVERNAME\FOLDERNAME\db1.mdb' )...tblOrders AS tblMDBOrders

Set that as a view and then Union the view with the real table. You will have to come up with your own connection string. This is really fast too.

 

[satinsilhouette]

My apologies, I thought you were asking how to bring two datasources together in a biz view.

In Business Views you would have to write a union select statement in sql analyzer or toad and then transport that code into a sql command object in the biz view.



Thanks so much!
satinsilhouette