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

[former_member542134]

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!

[former_member11696 · Accepted solution]

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

[former_member542134 · Accepted solution]

Don, Thanks for the answer! Appreciate it! We do not have CR 2016, but we have CR 13. Does CR 13 designer support the Native 11 driver?

Thank you,

Masood

[former_member11696 · Accepted solution]

As long as it's updated to SP 4 or above I believe is when we added Native 11 support.

Don

[former_member11696]

CR 2011 does support OLE DB.

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

Don

[former_member542134]

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

[former_member11696]

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

[former_member542134]

Hello Don, Thanks for your prompt reply to my question!

By OLE DB Provider client, do you mean the SQL Server Native Client 11.0 driver? If yes, then I have it installed for both 32 bit and 64 bit (I can see it listed under the Drivers tab of ODBC (32 bit and 64 bit) - file name SQLNCLI11.DLL).

Please correct me if I am going wrong.

Also, in the report, I have selected "Microsoft OLE DB Provider for SQL Server" as the provider and not "SQL Server Native Client 11.0".

When I select "SQL Server Native Client 11.0" as the OLE DB Provider to set up the OLE DB connection, then I get the following error:

FYI, I have CR X11 (Version 11.0.0.1282) installed on my PC. I am now testing the reports by pointing the connection string to the TEST server. The report is working only when I configure the Data Source's server name with the same server to which my connection string is pointing to. Once I switch the connection string to point to DEV server the report does not work. The report opens and prompts to enter parameter values. Once I enter parameter values click on Ok then I get the following error:

Please advise. Thank you!

[former_member542134]

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

[former_member542134]

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