Crystal reports from SQL-Anywhere to SQL-server

Question

Crystal reports from SQL-Anywhere to SQL-server

ben.brugman

SSChampion

Points: 13369
More actions
February 3, 2021 at 1:02 am

#3840338

Hello all,
We are in the process of replacing SQL-Anywhere with to SQL-Server.
Over a year ago I have been asked to see what the possibilities where and the problems. (But at that moment this was not considered an actual option, because of the clients and because of the cost of changing over).
More recently it became more urgent, after a proof of concept, we decided switching from SQL-Anywhere to SQL-server would be a good thing. The logging of SQL-Anywhere was extensively used for a large number of functions. Things like Auditing, replaying actions, checking what users did, repairing program and user errors. All these functions are now accounted for.
But now I hear, that the Crystal reports al have to be 'reprogrammed' by hand. And that there is 'no' simple ODBC switch or something similar. I am wondering if a shop like ours can exchange SQL-Anywhere for SQL-Server with limited difficulty, it is to me rather incredulous that this would not be available in a product like Crystal reports.
Any thoughts on this, or any good suggestions?
I have very limited experience, actually no experience with Crystal report myself. The above looks rather limited to me.
Thanks for your time and attention,
Ben

.....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

terry999 SSCertifiable Points: 5036 More actions · Answer 1

Never used SQL anywhere and I haven't used Crystal Reports for many years. This is from memory so might be wrong. Assuming your tables are identical in MSSQL Server and SQL anywhere.

In crystal open a report try database location\expert the top node you COULD find a name of OELDB/ODBC connection e.g. "LiveDSN" under this will be the Database objects each tables/views your report uses.

You can use ODBC Datasource to reconfig the datasource to point to another DBServer here is where you choose what ODBC/OLEDB driver to use (SQL Server, Access etc) . You use ODBC admin app (odbcad##.exe ) in windows (type run "odbc data..").

You need to create the datasource in the right version of the odbcad##.exe. There is a 32bit and 64 bit version, if crystal is 32 bit you need to use 32bit odbc admin. Create a new ODBC connection and call it LiveDSN i.e. same as what you found used in the report.

I did find some reports where a SQL authentication user had been hard coded inside the DSN and that account had been disabled. Re-enabling worked.

It depends on how the report is invoked..

The most solid approach, if possible, is to attach to each report using the Crystal report engine API and use a call to setDbLocation before calling/opening the report for the end user. Obviously this requires writing code to invoke/open the report.

You may have another app, e.g. some CRM systems that calls Crystal reports and this maybe setting the dblocation via code.

 For Each oTable In MyReportObject.Database.Tables

        oTable.SetLogOnInfo "LIVEDSN", DatabaseName
Next

ben.brugman SSChampion Points: 13369 More actions · Answer 2

Thanks,

Passed this information on to the team.

We just made a connection to the database, with some struggles. But succeeded. Next problem. Although the table names were the same the default schema name in SQL-Anywhere is DBA where the default in SQLserver is DBO.

To resolve that we made a View of each table with the same name, but in the DBA schema. In the long run the proposal is to fix this in the reports, but for now this is an effective workaround. Fairly elegant and easy to implement.

Thanks again,

Ben

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

ben.brugman wrote:

Thanks, Passed this information on to the team.
We just made a connection to the database, with some struggles. But succeeded. Next problem. Although the table names were the same the default schema name in SQL-Anywhere is DBA where the default in SQLserver is DBO.
To resolve that we made a View of each table with the same name, but in the DBA schema. In the long run the proposal is to fix this in the reports, but for now this is an effective workaround. Fairly elegant and easy to implement.
Thanks again, Ben

Instead of creating views - you can use synonyms. The synonyms will just be another name for the same object instead of code. For example:

CREATE SYNONYM dba.Table1 FOR dbo.Table1;

This also allows for creating a synonym of an existing views, procedures, functions, etc... - instead of having:

CREATE VIEW dba.MyView
AS

SELECT *
  FROM dbo.MyView;

Any changes to the view/stored procedure/function will be automatically available to the synonym.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

Instead of creating views - you can use synonyms.

This gets my vote too.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ben.brugman SSChampion Points: 13369 More actions · Answer 5

Thank you Jeffrey and Phil,

This is even better (was already happy with the solution), but this is even more elegant,

Thanks,

Ben