Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Using two database on two different servers Post Reply Post New Topic
Author Message
ichachan
Newbie
Newbie


Joined: 12 Oct 2009
Location: United States
Online Status: Offline
Posts: 2
Quote ichachan Replybullet Topic: Using two database on two different servers
    Posted: 21 Oct 2009 at 4:31pm
Hello,

I'm new at Crystal Report, can anyone please help me?

I'm trying to grab data from two separate database on two different servers. Let's say Database_A and Database_B. I linked a table in Database_A with a table in Database_B as a left outer join (link is not enforced).

However for some reason it never returns any results. I do get correct results when only working on Database_A, but not when Database_B is joined.

I'm getting suspicious when I check on 'Show SQL Query', it actually returns two queries that are not related to each other. It looks like something like this:

SELECT "Database_A".*
FROM   "Database_A".dbo."table_A" (and outer joins with table B, table C etc)
 WHERE  (Insert long where statement here...)


SELECT "Database_B".*
FROM   "Database_B".dbo."table_B"
WHERE  (Insert long where statement here...)

Are they supposed to be in one query if the join works? I'm really really confused.... can anyone help me on this one? I've been working on this report for about a month... not a rush one... but I don't feel good to my manager about this Dead

What would be the best practice to join two database from two different servers?

Thanks in advanced for your help!


-Elizabeth







Edited by ichachan - 21 Oct 2009 at 4:31pm
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 23 Oct 2009 at 6:33am

That you can even look at the 2 databases is a surprise to me, since most reports are created via a connection and only 1 connection is allowed, and it usually to just 1 db.

Are you running this from an application or stand alone?
Can you create stored procs?
 
My first solution/preference would be to create a stored proc on 1 database and have it get the information that is needed from the second database, then pass it all back to the report, or if you have a application, perhaps, push it to the report.
 
You can filter in the stored proc, or you can let CR do the filtering, again, I prefer to have the DB to the heavy lifting of number crunching/filtering/grouping and leave the formatting to CR.
 
Hoping to give an idea to the solution
IP IP Logged
ichachan
Newbie
Newbie


Joined: 12 Oct 2009
Location: United States
Online Status: Offline
Posts: 2
Quote ichachan Replybullet Posted: 26 Oct 2009 at 10:11am
Lockwelle! Thank you for your reply, greatly appreciate it!

I'm trying to create a stored procedure now on one of the database. The stored procedure will access the information from the other database.

Now how do I make Crystal Report to access the Stored Procedure? Do I have to run it on the database expert section?... still very confused here..

Thank you very much for your help!

Regards,
Elizabeth
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 26 Oct 2009 at 1:26pm
where you select tables, instead tables, just  a couple of lines below, is Stored Procedures...find the one that you want.  Database Expert is one way to get there. 
 
The change will alter the report, making the fields that were on the report inaccessable as the datasource will have been removed.
 
HTH
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.