combining multiple databases

[cardimom]

Hi,

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.

Thanks
Di

 

[IdoMillet]

You can link to all data sources (using MS Access or SQL Server DTS, or...). Then, create a UNION command or View as the data source.

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[cardimom]

I tried that but I keep getting errors. I can see all my servers in the Data expert, but all the tables are the same so when I go to add the other servers tables I get "The table "engine.machines" has already been added to this report with alias "engine.Machines", do you really want to add another alias to this table? I say yes and then I see machines and machines_1. Only the correct data of one of the servers shows in a report so I know they are not linking correctly. I add SELECT "Machines"."MachineName"
FROM "engine"."Machines" "Machines" UNION SELECT "Machines_1"."MachineName"
FROM "engine"."Machines_1" "Machines"

And get a Table "Machines_1" not found.

I have three tables from seven servers and I cant even get one table from two servers to work.

 

[IdoMillet]

A UNION SQL statement looks like:

SELECT * From Table1
UNION ALL
SELECT * From Table2
UNION ALL
SELECT * From Table3

In other words, you don't JOIN, you UNION... !

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

This depends on the type of database you're using.

You might just use a Command object and paste in the appropriate SQL, but since you are asking about database issues, but not posting the datbase type/version, it's difficult to help.

When asking about a database, please include the database type.

Also you should mention the connectivity type, this is basic information which directly impacts the solution.

Your syntax loked bads anyway as you were using the same alias for each table, try:

SELECT "Machines"."MachineName"
FROM "engine"."Machines"
UNION ALL
SELECT "Machines_1"."MachineName"
FROM "engine"."Machines_1"
etc...

-k

 

[cardimom]

Thanks, the databases are Veritas Netbackup Professional. I use ODBC(RDO) to connect. Using UnionAll I still get table.machines_1 not found. Do I have to distinquish between the servers somehow. The report shows all the backup profiles and how many pcs are assigned to each profile. Everything is exactly the same except for the server names.

 

[synapsevampire]

If there are different serverrs, then you might, but I'm unfamiliar with your database/ODBC driver to know whether this is supported.

You might try:

SELECT "Box1"."Machines"."MachineName"
FROM "engine"."Machines"
UNION ALL
SELECT "Box2"."Machines_1"."MachineName"
FROM "engine"."Machines_1"
etc...

Otherwise a cheat would be to link (not import) all of the tables into an MS Access database and create the query in Access, then use the query as the datasource in Crystal.

-k