SQL Query: Using tables from multiple databases

[bdukes]

Greetings:

I'm noticing when using two tables in a report, from two different databases on the same SQL Server, that after executing the report and showing the SQL Query, that there is no referrence to the second table even thought there are columns from the seccond table being displayed on the report.

For example Table A(database CEDARS_UTILS) is Inner Joined to table B (database CEDARS)

The columns that are diplayed one the report are A.PAT_ID and B.PAT_NAME.

When revieing the SQL query after executing, I see the following SQL:

SELECT "A"."pat_id"
FROM "CEDARS_UTILS"."dbo"."A" "A"

Does anyone know if this is a glich in crystal reports or just a limitation of using two different database tables in the same report? I'm using Crystal Reports 9.2 and SQL Server 2000.

Thanks,
Brent

 

[vidru]

I try to avoid using multiple data sources - Crystal has never seemed to like it, even though it allows it.

In your case, if both databases are on the same server, perhaps you can create a View in one of the databases that gathers the information from both. That way, Crystal would only need to connect to one source:
[TT]
CREATE VIEW ViewName
AS
SELECT A.PAT_ID, B.PAT_NAME
FROM CEDARS_UTILS.dbo.TableA A
JOIN CEDARS.dbo.TableB B ON A.LinkField = B.LinkField
[/tt]
-dave

 

[bdukes]

Thanks Dave!

I also do try to avoid using objects from different databases in the same report as I've seen other problems crop up as a result, but this issue was something that I hadn't seen before. I'm also not finding any documentation from Business Objects outling some of the issues that are created by using multiple databases in the same report.

Brent