Crystal w/Oracle & SQL Server 2

[bednarjm]

I've got a conversion project to convert some reports from straight SQL Server to Oracle for one of the tables and SQL Server for the others. The calling program is VB6 (ugh). Crystal version is 9.2. The other catch is that the normal access to the Oracle data is trrough a java web-service. I've already written a .net 'wrapper' that uses COM interop for this portion of the app to pass to vb6.

I have come up with several ideas. I'm not sure how to procede with these:
1) Link the reports through a view on either sql sever or oracle; (don't know if possible)
2) Create a temp table on sql server for the data. I would use my .net 'wrapper' to load the data to the temp table and then process the report; (performance would be a problem)
3) Create another COM object in .net to load the two data sources (from a collection class) and process using ado.net data tables and pass the resulting recordset back to the vb6 app to pass to crystal; (performance would again be bad)

Any other ideas?

thanks,
jim b

 

[synapsevampire]

I don't think that #1 is possible, and numbers 2 or 3 will be dependent upon the data. If the Oracle data is small, then it might make sense to dump it to the SQL Server database and then use a View or SP to expose the data to CR.

You can also join the tables from within the CR designer, but the join types are limited, and performance will suffer if it's a large dataset.

In any case performance will suck if these tables are to be joined (you didn't give any specifics about the data nor output requirements, just that you have 2 data sources.

Another option would be to link (not import) the tables in an Access database and then expose a query for Crystal to report against.

-k

 

[bednarjm]

Thanks,

The Oracle data can be large.

Access is not an option. We cannot use for 'enterprise' applications.


I'm going to pursue the COM 'wrapper' in .net. If I can;t get to work correctly maybe I can go back to our 'enterprise architect' who took away the portion that currently lives on sql server and will soon live on oracle. They (the unix heads) are attempting to push an architecture that doesn'tseem to work in the 'real-world'.

 

[synapsevampire]

I suffer from multiple data sources at many contracts, and unfortunately the common, quick solution is to link to an Access database to provide a reusable, easily maintained data source.

The current contract can't use Access as an Enterprise solution either, but we often do so anyway because there isn't a reasonable alternative.

Good luck with it.

-k

 

[vidru]

I'm pretty sure you can set up a linked server on the SQL Server to get to the Oracle db (don't know if you can do the inverse). With the linked server, you could set up a view on the SQL server that gets date from both.

Found this from SQL Server Magazine:

http://www.winnetmag.com/SQLServer/Articles/ArticleID/22264/pg/1/1.html

-dave

 

[synapsevampire]

Interesting article, Dave, thanks*.

I'll bet the local oracle dba's will come unglued when they find that people are using the SQL Server engine to handle this...

-k

 

[vidru]

You gotta do what ya gotta do, k ; }

-dave

 

[lyanch]

Oracle can probably handle the SQL server as well. It would handle cross links to our Sybase tables. Since it was a costly "add on" that would only be in use until we boot Sybase out the door we opted out. I handle most cross DB reporting with subreports. It isn't always pretty but for most things it works adequately.

Lisa

 

[bednarjm]

I'll bet the local oracle dba's will come unglued when they find that people are using the SQL Server engine to handle this...

Yes they will. However the Oracle database is the enterprise database and sql server is only for departmental level databases. I know they will not allow a link to live on Oracle.

You gotta do what ya gotta do, k ; }

Amen to that.


Thanks to all,
jim b

 

[sstrobel]

As an Oracle/SQL DBA, I have to answer many of these types of questions from developers regarding this. All of the above are possible, depending on the business case behind it. If the DBA (or the department) is responsible for both databases, they should not have a problem linking from a security perspective, but the performance issues may be a problem.

An idea to try is to "replicate" (Oracle Materialized Views and whatever SQL calls there replication, not linking mechanism) the data to one data source or the other. Depending on data volumes and business requirments the replication could be painfull or painless. Oracle does support replication from to non-oracle data source through hetergenous services. Note that Materialzed Views would support only a complete refresh and not a fast refresh in a non-oracle datasource.

The goal is to get the query to run within one database engine without having to copy the data over at run time.

 

[synapsevampire]

sstrobel: Replication (I've used snapshots many times in the past) always left a bad taste in my mouth, seemingly defeating the purpose of maintaining a single database, and the volumes of my current contract would make it a resource intensive proposition.

We do have similar processes in place, but we handle it by aggregating disparate data in star schemas (data marts).

Currently on Oracle servers, but we're moving it all to DB2.

-k