Post reply

Crystal reports from SQL-Anywhere to SQL-server

  • 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

     

    .....

     

     

     

  • February 3, 2021 at 12:45 pm

    #3840497

    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

     

  • February 5, 2021 at 2:52 pm

    #3841538

    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

     

  • February 5, 2021 at 4:29 pm

    #3841570

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • February 5, 2021 at 5:35 pm

    #3841597

    Instead of creating views - you can use synonyms.

    This gets my vote too.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • February 5, 2021 at 8:11 pm

    #3841674

    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