Crystal reports from SQL-Anywhere to SQL-server

  • 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

     

    .....

     

     

     

  • 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

     

  • 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

     

  • 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

  • Instead of creating views - you can use synonyms.

    This gets my vote too.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Thank you Jeffrey and Phil,

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

    Thanks,

    Ben

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

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