Multiple SQL databases 1

[rockman20]

I sure hope that this will be a simple solution. I know that I have gotten TONS of help on here before.

Here is my setup. CR 8.5 pulling from SQL2000 using ODBC connection.

My knowledge of SQL is small and my knowledge on CR is basically what I have taught myself from reading through books and on here.

I have approximately 22 different databases on 2 different servers. I need to combine data from a particular table in each database and these tables all have the same structure.

Basically I need to combine the table ASTUXXXX (where XXXX is the year and school number. Such as ASTU4004 or ASTU4006, etc) into basically one large table and then query information from that.

What would the best way be to go about this? Please keep in mind that my knowledge is limited! Thanks in advance for all the help!

 

[IdoMillet]

Use one of the databases to link to all the others.

Create a View that combines all the table into a single result set using:

SELECT * FROM ASTU4004
UNION ALL
SELECT * FROM ASTU4006
UNION ALL
SELECT * FROM ...

Then, use the VIEW as the data source in your Crystal report.

hth,
- Ido

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

http://www.MilletSoftware.com

 

[rockman20]

I may be missing something here. Do I add all of the databases into my report? If I start adding multiple databases, my Show SQL Query disappears.

Or do I just add in my primary database into the report and then just type in the other ones in my SQL Query and is Crystal smart enough to know to connect to that database?

I'm sorry for the lack of knowledge on this, but I do really appreciate the help!

 

[IdoMillet]

The linking to the other databases is done is SQL Server.
The creation of the View is done in SQL Server.

The report then uses the SQL Server View.

hth,
- Ido

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

http://www.MilletSoftware.com

 

[rockman20]

Really showing my lack of experience here.......

I got a view created from multiple tables from multiple databases on one SQL server. Now, how can I create one view, on one server, that contains ASTU tables from SQL server 1 databases AND SQL server 2 databases.

PS I love the help. It has given me a chance to play more with SQL then what I get to. Thanks for the help!

 

[rockman20]

Never mind!

I love this forum. They make you work for your answers. I managed to search through the SQL programming forum and I managed to find a way to create a view with a linked server and I actually got it to work.

So now I am back in Crystal and it looks like my view is actually pulling correctly. Now to do a little data investigation and I will see what I can come up with. This has been a lot of fun and if I could give IdoMillet a million stars I would!

Thanks for the fun and the investigation!

 

[IdoMillet]

Been away, so didn't see you follow up question. Glad you got it working. Views are a powerful tecnnique for solving many other challenges.

Cheers,
- Ido

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

http://www.MilletSoftware.com