Crystal Reports - data joining

[Honyo]

Hi

I currently have 2 sql servers running (Denver and Vancouver). Both have identical databases, tables and columns. The data in these tables is however different - Denver all starts with 1 and Vancouver all with 2.

I have create 2 seperate reports and both connect to their respective databases and pull all the neccessary data. When I try and connect to both the databases, it insists that I create an alias for the second field of the same name. I need to run a report that combines the data from these 2 tables. The data will be sorted on by datetime. Sub reports don't work!

Please can someone help ! !

Regards
Honyo

 

[IdoMillet]

Create a SQL Query (in Ceystal SQL Designer if you don't have CR9 or as a "Command" if you do have CR 9) or a View/Stored Procedure (using linked data source withing SQL Server) that utilizes a UNION to combine the two result sets into one.

For example:
-------------------------------
SELECT * FROM MYTABLE_A
UNION ALL
SELECT * FROM MYTABLE_B
-------------------------------

UNION allows SQL statements to be "appended"
to each other provided the number & data type
of all columns match.

Cheers,
- Ido

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

http://www.MilletSoftware.com

 

[Honyo]

Thanks Ido.

My problem is that the records are sitting in 2 seperate SQL server machines. I got the union to work but only if the tables are on the same server. I need to something to bridge the 2 servers and then join the tables so that I can pull the records vai Crystal.

Regards
Honyo

 

[shokti]

use setlogoninfo to change sqlserver. when using setlogoninfo make sure that you will specify the sqloledb provider together with the server name in the server name parameter. it works for me since i have switch server when i deploy the program to the live data server.

 

[lyanch]

Your only hope is if you can set up a trusted connection between the two servers. Then you can combine the data in a stored procedure or view.

I don't believe there is anyway to do this from within crystal itself.

*hoping for your sake that someone proves me wrong*

Lisa

 

[synapsevampire]

Or you might consider setting up a scheduled job to copy the data from one server to the other with some reasonable time frequency to provide reasonably fresh data.

Otherwise I agree that the solution is within a View or SP.

-k

 

[scottostanek]

I've been drudging through a similar problem and found one way to handle it is to use another step in between. I used Microsoft's Excel Query with a query on one tab to get the data from one side and a second ODBC bound query to get the rest of the data in another tab. Five or so lines of vba (really just a macro) copies the results of those tabs to a third.

Then I used crystal's Excel wizard to make that tab a data source. Ran the report against it, whala! It does require one to open the excel file and click a button (refreshes the data and copies all to third tab), but it is a useful workaround.