Skip to Content

How to update the data source's Server Name of a crystal report that is using an OLE DB provider?

Hello, I have a dozen of crystal reports which have been designed to connect to a database on Development Server (for example, Database: XYZ and Server name: DEV). The reports use OLE DB provider to connect to the data source. Also, the report uses various database fields in the designer to print the data.

Now, to make these reports work for on the Test Server for the same database (for example, Database: XYZ and Server name: TEST), I open the reports and update the DataSource's Server Name from "DEV" to "TEST" (still using the OLE DB connection).

I use these reports in a .NET web application (C#) that uses the free Crystal Report Viewer 13 to run the report. When I am working on DEV server, I am using the set of reports whose DataSource's Server Name is set to "DEV", but when the application is moved to the TEST server I am having to use the other set of reports whose DataSource's Server Name is set to "TEST" otherwise, I am getting database logon failed error.

For each report, we fetch the server name, database name, userID, and Password from our application's database and set them to the ConnectionInfo as shown in the code snippet below:

var report = new ReportDocument();
report.Load(filename: Server.MapPath(p));
            
var ciReportConnection = new ConnectionInfo
{
    ServerName = zServer,
    DatabaseName = zDatabase,
    UserID = zUsername,
    Password = zPassword
};

foreach (CrystalDecisions.CrystalReports.Engine.Table table in report.Database.Tables)
{
    table.LogOnInfo.ConnectionInfo = ciReportConnection;
    table.ApplyLogOnInfo(table.LogOnInfo);
}

if (this.CrystalReportViewer1.LogOnInfo != null)
{
    TableLogOnInfos tlInfo = this.CrystalReportViewer1.LogOnInfo;
    foreach (TableLogOnInfo tbloginfo in tlInfo)
    {
        tbloginfo.ConnectionInfo = ciReportConnection;
    }
}
           
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;

The above snippet makes sure to set the DataSource's Server Name dynamically to "DEV" if it is running on DEV server and to "TEST" if it is running on TEST server, but still when I move the code to TEST my reports are not working and I am getting a log on failed error. I am assuming that it is because the reports that have been configured with "DEV" server name are not working on the TEST server. On the other hand, the reports work fine on TEST server if I use the set of reports that have been configured with DataSource's server name as "TEST".

Is there any way, I can update the DataSource's server name dynamically? Any idea where I am going wrong with my C# code?

I cannot remove the data source and the database tables from the report as it will mess the design of the report. I am looking for a way to make these reports work on all the environments (DEV, TEST, and PROD) using OLE DB connection. FYI, the database name on all the servers is same. Any help would be appreciated! Thank you!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Nov 15, 2017 at 05:11 PM

    What version of Crystal Designer are you using?

    CR XI will not support the Native 11 driver and no updates for it, MS doesn't fully support the MDAC OLE DB driver.

    You need to upgrade the Designer to CR 2016.

    Don

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 09, 2017 at 08:59 PM

    It could be you are missing the OLE DB Provider client install on the app server.

    If your app is running in x86 mode then you need the x86 MSI package and the 32 bit DB client installed and verify it works.

    Don

    Add comment
    10|10000 characters needed characters exceeded

    • Hello Don, I was advised to get CR 2016 Designer ast CR 2011 does not support OLE DB. My question is that do we have to purchase Crystal Reports Designer 2016 and set up OLE DB connections in the report or just installing free Crystal Reports run-time on the server would be enough?

      Thanks,

      Masood

  • Dec 13, 2017 at 04:29 PM

    CR 2011 does support OLE DB.

    What happens if you create a new report using OLE DB?

    Don

    Add comment
    10|10000 characters needed characters exceeded

    • Don, I have not created a new report from scratch using OLE DB, but I used the old report which was created using ODBC and then replaced the ODBC connection with the new OLE DB connection. I created a new OLE DB connection using the "SQL Server Native Client 11.0" provider and then set the Data Source, User ID, Password, and Database. Once the OLE DB connection is created I replaced the old ODBC connection of the report with the new OLE DB connection.

      The report works fine on DEV server if the Data Source I set, when creating OLE DB connection, is pointing to the DEV database server. The same report when moved to TEST server and then the .NET application tries to run it against the TEST database server then I am getting the following errors:

      logon-error.png

      error.png

      In the .NET code I am setting the data source information as shown in the snippet below:

      var report=new ReportDocument();report.Load(filename: Server.MapPath(p));
                  
      var ciReportConnection =new ConnectionInfo
      {
          ServerName = zServer,
          DatabaseName = zDatabase,
          UserID = zUsername,
          Password = zPassword
      };

      But it is still giving me logon errors. Is it because when I initially replace the ODBC connection with the OLE DB, I set the Datasource to point out to DEV server? Shouldn't the datasource name dynamically change based on the server it is running on (DEV or TEST) using the above snippet?

      Please advise. I am stuck with this issue. I can share more information and elaborate if you want. I just want a report to work on all environments (DEV, TEST, and PROD) by dynamically changing it's datasource name through the C# code. In this way I can avoid creating three different versions of a report for each environment.

      Thanks,

      Masood

      logon-error.png (8.0 kB)
      error.png (16.3 kB)
  • Dec 21, 2017 at 10:15 PM

    Don,

    I found that the ConnectionInfo for tables was not being set when move a report from DEV to TEST even though I was setting it in the code. After playing around with the C# code a number of times the following snippet of code seems to work fine with the different data sources no matter in which environment (DEV, TEST, or PROD) the report is placed. This snippet seems to resolve the error I was getting.

                var report = new ReportDocument();
                report.Load(filename: Server.MapPath(directoryPathOfReport));
    
                TableLogOnInfo logOnInfo;
                foreach (CrystalDecisions.CrystalReports.Engine.Table table in report.Database.Tables)
                {
                    logOnInfo = table.LogOnInfo;
                    logOnInfo.ReportName = report.Name;
                    logOnInfo.ConnectionInfo.ServerName = zServer;
                    logOnInfo.ConnectionInfo.DatabaseName = zDatabase;
                    logOnInfo.ConnectionInfo.UserID = zUsername;
                    logOnInfo.ConnectionInfo.Password = zPassword;
                    logOnInfo.TableName = table.Name;
                    
                    table.ApplyLogOnInfo(logOnInfo);
                    table.Location = table.Name;
                }
    

    I looked into some old question and answers from the archives and came to know that for some versions of Crystal Reports we need to set TableName and Location as well within the foreach section. I am still not sure the reason for it not working before.

    The snippet of code which was not working previously is pasted below:

     var report = new ReportDocument();
     report.Load(filename: Server.MapPath(p));
    
     //Get SQL Server Details
     string zServer = app.Hivrp_server_name;
     string zDatabase = app.Hivrp_database_name;
     string zUsername = app.Hivrp_user_id;
     string zPassword = app.Hivrp_password;
    
     var ciReportConnection = new ConnectionInfo
     {
         ServerName = zServer,
         DatabaseName = zDatabase,
         UserID = zUsername,
         Password = zPassword
     };
    
     //Assign data source details to tables
     foreach (CrystalDecisions.CrystalReports.Engine.Table table in report.Database.Tables)
     {
         table.LogOnInfo.ConnectionInfo = ciReportConnection;
         table.ApplyLogOnInfo(table.LogOnInfo);
     }
    
     CrystalReportViewer1.RefreshReport();
     CrystalReportViewer1.ReportSource = report;

    If you have seen this issue before and know the reason behind it then please let me know.

    I am using Crystal Reports 11.0.0.1282. The above solution is working whether I use "Microsoft OLE DB Provider for SQL Server" or "SQL Server Native Client 11.0".

    Previously, I was not able to set OLE DB connections using "SQL Server Native Client 11.0" using Crystal Reports 11.0.0.1282. But I am now able to set OLE DB connections using "SQL Server Native Client 11.0" using Crystal Reports 2013 SP 1 (Version 14.1.1.1036). I think as you mentioned in one of the above answers, CR 11 does not support SQL Server Native Client.

    Thanks,

    Masood

    Add comment
    10|10000 characters needed characters exceeded