How to handle DB2 and SQL server in a report

[rajrev]

Hi,
We have a problem at the time of linking the DB2 and SQL server tables.
I'll give the breif discription:
A report contains fields which are from DB2 tables.
and this report has 10 parameters 5 of them are from DB2 and 5 of them are from SQL server table.
Now,
I linked the Master table (which is from SQL Server) with Employee table (which is from DB2 database)using the primary key Master.EmpID--->EMployee.EmpID.
And other tables are liked from Employee table (here other tables means other DB2 tables). Up to this every thing is fine.

But at the time of adding a SQL server table other then the Master, I got the ODBC Error. for example,
we have a field name called EmailID in Master----> link with Position.Emailid (which contains the position names, used at the time of filtering the report (ie it is one of the parameter)
It's kind of 2 links from same sql Master table --> one is for DB2 (using EmpID) and anoter one is for SQL table (using EMAILID).
Note:
If i test the sample report with only SQL db tables its working fine.

I'm not sure How clearly I explain my problem. Please let me know if you need more info/clarification.
Thanks
MK

 

[rajrev]

Need more info. please let me know.

 

[synapsevampire]

Try posting specifics, such as the version of Crystal, the connectivity used for each database (you said ODBC, is that the CR supplied ODBC driver for DB2?), are you using native SQL Server or an ODBC driver?).

And then you mention an error, but you don't state the error given...

Going across 2 databases in Crystal tends to be VERY slow, a common alternative is to LINK (not import) the datasources within an Access database and creating a Query to return the rows required, and then using that query as the data source for a Crystal report.

There are gateway products also, here's a blurb on one:

http://www.microsoft.com/sql/evaluation/compare/ibm/extremelogicdemos.asp

-k

 

[rajrev]

Thanks -k.. sorry about the previous posting (I'm in the rush on that day.)

Crystal Reports 8.5
we created DSN names for SQL Server and DB2 Database through Control panel-->Admin.tool-->Datasourse(ODBC).

And the error popup window says "ODBC error: " and OK button. after click on the OK button then, a new window says,"Error deducted by database DLL". thats all the error messages.
At present we are using the Access for handling the SQL server database (as .adp file).
But I'm not that much familer with connecting 2 database in a same report.
About LINK - 2 database through Access!, could you please explain in detail.

Need more info. please let me know.

Thanks,
MK

 

[rajrev]

I forgot to menstion about the Driver
We are using CR DB2 V37 Driver

 

[lyanch]

It is virtually impossible to link two databases across server types in Crystal and have parameters on both of the databases. Crystal pretty much picks one to start with.. gets all the rows that match the parameters on that database, then gets the other half of the data from the other database, then does the "matches both" locally.

That is *if* you can get the two databases to link. I have only one report that sucessfully links two databases and I am not sure how (or why) it works when others using a virtually identical link won't (Oracle to Sybase but similar problem).

As -k suggested Access is probably the way to go, but it too can be slow. Check your help files in Access, there is a good explaination on how to Link tables (and is version dependant).

Lisa

 

[rajrev]

Thanks for your suggestion.
Do you people have any suggetion : what are the posiblities to make it up using subreport?
like link the SQL tables which are needed for parameter(formula condition) and compare main report parameter values with the sub report value... like that..

Thanks
MK