Merging multiple database data

[cardimom]

Hi,

I have asked this several times but never got a workable solution.

I have seven servers that I would like to combine the data from into one report. The Data that is being pulled is from the same fields on all of them. Right now I run them separately and manually go through them and combine the data which is quite time consuming. I can make subreports but that does not combine all the same data. Is this possible and how. I am using crystal reports 9. I could also use Access or Excel, have tried those with out any success. The databases are Adaptive Server Anywhere being used for veritas netbackup professional.

Thanks
So much
Di

 

[rchandr3]

Its possible if you have the DBs in the same server and same username and password to access the data.

Cheers,

Ravi

 

[cardimom]

They do all have the same username and password, but because of the size of the DBs they cant all be on the same server.

 

[fredp1]

Hi,
Their are a number of options.
1. use a union query in crystal. see thread

http://www.tek-tips.com/viewthread.cfm?qid=1200068&page=1

as an example.

2. use Access by linking to each of the tables in each db. Then create a union query in access. Use the Access union query as the db source in cr.

3. I'm not sure about your particular db, but, in SQL Server, you could create a union query linking all 7 db's, very similar to the access solution.

If the db supported the union query, i'd use that option first. Check/google your db for a example.
Next option would be a union query in cr, then followed by Access. Excel may not work due to the limitation of 65k rows.
If you need more details, i'm happy to post.
Fred

Cheers
Fred