Database Schema Lost when changing Report Connection Info

[#1]

I am using the SAP Crystal Reports for Visual Studio .NET SDK to connect user defined reports to an InterSystems Cache database to display reports. The database being reported against may change at runtime (running against a different database server than the report was actually developed against), but the database structure will be the same. As a result, I do not want to incur the cost of a VerifyDatabase.

I found some posting recently that indicated that the method I am using to connect to the new datasource (databaseController.SetTableLocation(table, newTable)) actually invokes VerifyDatabase every time it is called. So, I rewrote my code to try other methods:

• table.ApplyLogOnInfo(tableLogonInfo);
• report.ReportClientDocument.DatabaseController.ReplaceConnection(oldInfo, newInfo, null, CrDBOptionsEnum.crDBOptionDoNotVerifyDB);
• dataSourceConnection.SetConnection("NXTESTING", crystalReportConnection.NameSpace, crystalReportConnection.UserId, crystalReportConnection.Password);
• report.SetDatabaseLogon(username, password, odbcDataSource, string.Empty, true);

The problem with each of these methods is that if there are tables that use a different schema other than the default (SYSTEM), then the schema is removed from the table and the query will not run (I receive a "Failed to retrieve data from the database. Details: [Database Vendor Code: 30]", which indicates the table or view is not found.) I have verified that after setting the credentials using one of the non-SetTableLocation methods, the table.QualifiedName changes (from SYSTEM.zip_code_table, to just zip_code_table). I have tried just resetting the table.QualifiedName back to its original value, but that does not help either, as I receive the same error message (Code 30).

Is there a faster method to provide database credentials to a new database that has the same structure as the one from which a report was originally created without having to incur the cost of calling SetTableLocation (and the resulting VerifyDatabase) for every table in the report and subreports, without losing the Schema from the QualifiedName of the tables? Or, is there a quick way to reset this after it is lost that will allow the report to connect to the correct tables?

[former_member11696 · Accepted solution]

Hi Brian,

You can fully qualify the connection info using ReplaceConnection() and something like this:

//For each table in the report:
// - Set the Table Name properties.
// - Set the table location in the report to use the new modified table
boTable.Name = "Orders";
boTable.QualifiedName = "xtreme.dbo.Orders";
boTable.Alias = "Orders";


rptClientDoc.DatabaseController.SetTableLocation(boTables[0], boTable);


boTable.Name = "Orders Detail";
boTable.QualifiedName = "xtreme.dbo.Orders Detail";
boTable.Alias = "Orders_Detail";


rptClientDoc.DatabaseController.SetTableLocation(boTables[1], boTable);


boTable.Name = "Customer";
boTable.QualifiedName = "xtreme.dbo.Customer";
boTable.Alias = "Customer";


rptClientDoc.DatabaseController.SetTableLocation(boTables[2], boTable);
#endregion Ludek


CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;

I don't have complete code to do so but it should work for you...

Use Google and search for this KBA: 1646498 - When to use the 'Replace Connection' method using the Crystal Reports or InProc RAS SDK for .NET

Blog has more info.

And use this one to get the code generated to use Replace Connection, compare the code before and after updating the report manually:

1553921 - Is there a utility that would help in writing database logon code?

CR keys a copy of the database and connection info in the report, so if the 2 DB's don't match CR will remove any reference to the missing fields, that's by design.

Don