Server Error in '/CrystalReportsWebSite3' Application.

Former Member · ‎02-09-2010

I would like to use the same report for several data base providers (ODBC, OLEDB SqlServer and OLEDB Oracle).

My original report is designed with ODBC by another department. When I try to change the data base provider to Oracle or SqlServer I have an error at line:

<code>

 //this line throw an exception if the report was not designed with the

same database provider.

switch (provider)

{

case Provider.SqlServer:

        crTable.Location =

String.Format("{0}.dbo.", crConnectionInfo.DatabaseName, crTable.Name); //database.dbo.tablename         break;     case Provider.Oracle:         crTable.Location = String.Format(".", crConnectionInfo.DatabaseName, crTable.Name);

//schema.tablename

break;

default:

break;

}</code>

Even setting the same location, like this crTable.Location = crTable.Location; the code throws an exception.

I have to change the report schema location at runtime because we have several databases (SqlServer) or schemas (Oracle) and we have to use the same report.

I use Crystal Reports Version=12.0.2000.0

Here is my complete code:

<code>

private CrystalDecisions.Shared.ConnectionInfo FixDatabaseSqlServer()

{

CrystalDecisions.Shared.DbConnectionAttributes dbAttributes = new

DbConnectionAttributes();

dbAttributes.Collection.Set("Server", this.ServerName.Text);

dbAttributes.Collection.Set("Trusted_Connection", "false");

dbAttributes.Collection.Set("Data Source", this.ServerName.Text);

dbAttributes.Collection.Set("Initial Catalog", this.DatabaseName.Text);

dbAttributes.Collection.Set("Integrated Security", "false");

dbAttributes.Collection.Set("Provider", "SQLOLEDB");

//setup the connection

CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new ConnectionInfo();

crConnectionInfo.DatabaseName = this.DatabaseName.Text;

crConnectionInfo.ServerName = this.ServerName.Text;

crConnectionInfo.UserID = this.UserID.Text;

crConnectionInfo.Password = this.Password.Text;

crConnectionInfo.IntegratedSecurity = false;

crConnectionInfo.Attributes.Collection.Set("Database DLL", "crdb_ado.dll");

crConnectionInfo.Attributes.Collection.Set("QE_DatabaseName",

this.DatabaseName.Text);

crConnectionInfo.Attributes.Collection.Set("QE_DatabaseType", "OLE DB (ADO)");

crConnectionInfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);

crConnectionInfo.Attributes.Collection.Set("QE_ServerDescription",

this.ServerName.Text);

crConnectionInfo.Attributes.Collection.Set("QE_SQLDB", "True");

crConnectionInfo.Attributes.Collection.Set("SSO Enabled", "False");

crConnectionInfo.Type = CrystalDecisions.Shared.ConnectionInfoType.CRQE;

crConnectionInfo.LogonProperties = dbAttributes.Collection;

return (crConnectionInfo);

}

private CrystalDecisions.Shared.ConnectionInfo FixDatabaseOracle()

{

CrystalDecisions.Shared.DbConnectionAttributes dbAttributes = new

DbConnectionAttributes();

dbAttributes.Collection.Set("Server", this.ServerName.Text);

dbAttributes.Collection.Set("Trusted_Connection", "False");

dbAttributes.Collection.Set("Data Source", this.ServerName.Text);

dbAttributes.Collection.Set("Provider", "MSDAORA");

//setup the connection

CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = new ConnectionInfo();

crConnectionInfo.DatabaseName = "";

crConnectionInfo.ServerName = this.ServerName.Text;

crConnectionInfo.UserID = this.UserID.Text;

crConnectionInfo.Password = this.Password.Text;

crConnectionInfo.IntegratedSecurity = false;

crConnectionInfo.Attributes.Collection.Set("Database DLL", "crdb_oracle.dll");

crConnectionInfo.Attributes.Collection.Set("QE_DatabaseName", "");

crConnectionInfo.Attributes.Collection.Set("QE_DatabaseType", "OLE DB (ADO)");

crConnectionInfo.Attributes.Collection.Set("QE_LogonProperties", dbAttributes);

crConnectionInfo.Attributes.Collection.Set("QE_ServerDescription",

this.ServerName.Text);

crConnectionInfo.Attributes.Collection.Set("QE_SQLDB", "True");

crConnectionInfo.Attributes.Collection.Set("SSO Enabled", "False");

crConnectionInfo.Type = CrystalDecisions.Shared.ConnectionInfoType.CRQE;

crConnectionInfo.LogonProperties = dbAttributes.Collection;

return (crConnectionInfo);

}

private void FixDatabase(ReportDocument report, Provider provider)

{

bool setLocationError = false;

string tableLocationPattern = String.Empty;

CrystalDecisions.Shared.ConnectionInfo crConnectionInfo = null;

switch (provider)

{

case Provider.SqlServer:

crConnectionInfo = FixDatabaseSqlServer();

tableLocationPattern = String.Format("{0}..", crConnectionInfo.DatabaseName, ""); break; case Provider.Oracle: crConnectionInfo = FixDatabaseOracle(); tableLocationPattern = String.Format(".", crConnectionInfo.UserID, ""); break; case Provider.Undefinded: default: break; } //set database login information for the entire report object report.SetDatabaseLogon(crConnectionInfo.UserID, crConnectionInfo.Password, crConnectionInfo.ServerName, crConnectionInfo.DatabaseName); CrystalDecisions.CrystalReports.Engine.Database crDatabase = report.Database; //Set the CrDatabase Object to the Report's Database CrystalDecisions.CrystalReports.Engine.Tables crTables = crDatabase.Tables; //Set the CrTables object to the Tables collection of the Report's dDtabase foreach (CrystalDecisions.CrystalReports.Engine.Table crTable in crTables) { TableLogOnInfo crTableLogOnInfo = null; crTableLogOnInfo = crTable.LogOnInfo; if (crTableLogOnInfo != null) { crTableLogOnInfo.ConnectionInfo = crConnectionInfo; crTable.ApplyLogOnInfo(crTableLogOnInfo); // //Set location. // try { //this line throw an exception if the report was not designed with the same database provider. switch (provider) { case Provider.SqlServer: crTable.Location = String.Format(".dbo.", crConnectionInfo.DatabaseName, crTable.Name); //database.dbo.tablename break; case Provider.Oracle: crTable.Location = String.Format(".", crConnectionInfo.DatabaseName,

crTable.Name); //schema.tablename

break;

default:

break;

}

crTable.Location = crTable.Location;

}

catch (Exception ex)

{

setLocationError = true;

//throw;

}

crTable.TestConnectivity();

}

//call this method recursively for each subreport

foreach (ReportObject reportObject in report.ReportDefinition.ReportObjects)

{

if (reportObject.Kind == ReportObjectKind.SubreportObject)

{

FixDatabase(report.OpenSubreport(((SubreportObject)reportObject).SubreportName),

provider);

}

}

</code>

I tried everything I found at forums with no success.

Please, Is there anyone with the same problem who had solved it?

Is it possible to have the same report and several database providers?

What have do I have to do?

Thank you very much.

Edited by: jporcar on Feb 9, 2010 9:41 AM

former_member183750 · ‎02-09-2010

I'd simplify things significantly.

1) do not use any subreports

2) create a report on MS SQL, make sure the code you use connects there

3) create a report off of Oracle, make sure the code you use connects there

4) the reports above should be one table, one field

5) the reports should be using the same type of connectivity (ODBC, or OLEDB)

6) reports using ODBC can not be made to go to an OLEDB datasource with the CR APIs for .NET. to change from ODBC to OLEDB and vice versa, you'd need to use inprocRas

7) once you have both of the reports working, try to switch them - first in the designer, then at runtime (e.g.; if these can not be changed in the designer, runtime will do the same)

Your Oracle connectivity code would look something like this:

Dim connection As ConnectionInfo 
connection = New ConnectionInfo() 
With connection 
.ServerName = Our Server Name Here 
.UserID = Our User ID Here 
.Password = Our Password Here 
.DatabaseName = Our Database Name Here 
.Type = ConnectionInfoType.SQL 
End With 

Dim CRTableLogOnInfo As TableLogOnInfo 
For Each Tbl As Table In Report.Database.Tables 
CRTableLogOnInfo =Tbl.LogOnInfo 
CRTableLogOnInfo.ConnectionInfo = connection 
Tbl.ApplyLogOnInfo(CRTableLogOnInfo) 
Tbl.Location = Our Schema Here & "." & Tbl.Location 
Next

Note that this is different than MS SQL... Also, note that Oracle is case sensitive.

Ludek

Former Member · ‎11-18-2010

Hi,

I am not able to dowload utility from given location. it says that link is expired.

Could you please reupload that utility.

Thanks,

Vikas

Edited by: vikasJaigude on Nov 18, 2010 10:20 AM

Former Member · ‎07-08-2010

Any chance we could get that utility re-uploaded? It sounds pretty useful!

Thanks!

Former Member · ‎03-12-2010

Unfortunately, this utility:

There is a utility on our ftp server that will give you the logon code you'd need for your reports. All you have to do is open the reprot with the utility and you will get VB and C# code. The utility is here:

https://sapmats-us.sap-ag.de/download/download.cgi?id=I8XEAX64QU762S0XC8J71OWPTWWJUQKHGZMNZMJSEFV7FJ...

is not available?

Former Member · ‎03-12-2010

Thank you - all the help is much appreciated - especially to know that when (thinking positively) I DO get it working, we'll still be able to distribute it to our customers!

Former Member · ‎03-11-2010

We have several unresolved difficultties with Oracle using the code below supplied by SAP.

But I hope it helps.

Access

<code>

//Add these required Crystal Assemblies to your project

//CrystalDecisions.ReportAppServer.DataDefModel

//CrystalDecisions.ReportAppServer.ClientDoc

//CrystalDecisions.ReportAppServer.Controllers

//CrystalDecisions.CrystalReports.Engine

//CrystalDecisions.Shared

//Add these Crystal Assemblies to the top of your code page

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

private ReportDocument ChangeConnectionInfo()

{

ReportDocument boReportDocument = new ReportDocument();

//*EDIT* Change the path and report name to the report you want to change.

boReportDocument.Load(@"c:\reports\yourreport.rpt",

OpenReportMethod.OpenReportByTempCopy);

//Create a new Database Table to replace the reports current table.

CrystalDecisions.ReportAppServer.DataDefModel.Table boTable =

new CrystalDecisions.ReportAppServer.DataDefModel.Table();

//boMainPropertyBag: These hold the attributes of the tables ConnectionInfo

object

PropertyBag boMainPropertyBag = new PropertyBag();

//boInnerPropertyBag: These hold the attributes for the QE_LogonProperties

//In the main property bag (boMainPropertyBag)

PropertyBag boInnerPropertyBag = new PropertyBag();

//Set the attributes for the boInnerPropertyBag

boInnerPropertyBag.Add("Data Source",

@"your_data_base.accdb");

boInnerPropertyBag.Add("Locale Identifier", "1033");

boInnerPropertyBag.Add("Office Database Type", "Access");

boInnerPropertyBag.Add("OLE DB Services", "-6");

boInnerPropertyBag.Add("Provider", "Microsoft.ACE.OLEDB.12.0");

boInnerPropertyBag.Add("Use DSN Default Properties", "False");

//Set the attributes for the boMainPropertyBag

boMainPropertyBag.Add("Database DLL", "crdb_ado.dll");

boMainPropertyBag.Add("QE_DatabaseName", "");

boMainPropertyBag.Add("QE_DatabaseType", "OLE DB (ADO)");

//Add the QE_LogonProperties we set in the boInnerPropertyBag Object

boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);

boMainPropertyBag.Add("QE_ServerDescription",

@"
Xaloc\epsilonnet\Dietario\Datos\DatosConfig.accdb");

boMainPropertyBag.Add("QE_SQLDB", "True");

boMainPropertyBag.Add("SSO Enabled", "False");

//Create a new ConnectionInfo object

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =

new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

//Pass the database properties to a connection info object

boConnectionInfo.Attributes = boMainPropertyBag;

//Set the connection kind

boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

//*EDIT* Set the User Name and Password if required.

boConnectionInfo.UserName = "UserName";

boConnectionInfo.Password = "Password";

//Pass the connection information to the table

boTable.ConnectionInfo = boConnectionInfo;

//Get the Database Tables Collection for your report

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

boTables =

boReportDocument.ReportClientDocument.DatabaseController.Database.Tables;

//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 = "TABLE1";

boTable.QualifiedName = "TABLE1";

boTable.Alias = "TABLE1";

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables[0],

boTable);

//Verify the database after adding substituting the new table.

//To ensure that the table updates properly when adding Command tables or Stored

Procedures.

boReportDocument.VerifyDatabase();

return boReportDocument;

}</code>

SqlServer

<code>

//Add these required Crystal Assemblies to your project

//CrystalDecisions.ReportAppServer.DataDefModel

//CrystalDecisions.ReportAppServer.ClientDoc

//CrystalDecisions.ReportAppServer.Controllers

//CrystalDecisions.CrystalReports.Engine

//CrystalDecisions.Shared

//Add these Crystal Assemblies to the top of your code page

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

private ReportDocument ChangeConnectionInfo()

{

ReportDocument boReportDocument = new ReportDocument();

//*EDIT* Change the path and report name to the report you want to change.

boReportDocument.Load(@"c:\reports\yourreport.rpt",

OpenReportMethod.OpenReportByTempCopy);

//Create a new Database Table to replace the reports current table.

CrystalDecisions.ReportAppServer.DataDefModel.Table boTable =

new CrystalDecisions.ReportAppServer.DataDefModel.Table();

//boMainPropertyBag: These hold the attributes of the tables ConnectionInfo

object

PropertyBag boMainPropertyBag = new PropertyBag();

//boInnerPropertyBag: These hold the attributes for the QE_LogonProperties

//In the main property bag (boMainPropertyBag)

PropertyBag boInnerPropertyBag = new PropertyBag();

//Set the attributes for the boInnerPropertyBag

boInnerPropertyBag.Add("Auto Translate", "-1");

boInnerPropertyBag.Add("Connect Timeout", "15");

boInnerPropertyBag.Add("Data Source", "localhost");

boInnerPropertyBag.Add("General Timeout", "0");

boInnerPropertyBag.Add("Initial Catalog", "your_data_base");

boInnerPropertyBag.Add("Integrated Security", "True");

boInnerPropertyBag.Add("Locale Identifier", "3082");

boInnerPropertyBag.Add("OLE DB Services", "-5");

boInnerPropertyBag.Add("Provider", "SQLOLEDB");

boInnerPropertyBag.Add("Tag with column collation when possible", "0");

boInnerPropertyBag.Add("Use DSN Default Properties", "False");

boInnerPropertyBag.Add("Use Encryption for Data", "0");

//Set the attributes for the boMainPropertyBag

boMainPropertyBag.Add("Database DLL", "crdb_ado.dll");

boMainPropertyBag.Add("QE_DatabaseName", "your_data_base");

boMainPropertyBag.Add("QE_DatabaseType", "OLE DB (ADO)");

//Add the QE_LogonProperties we set in the boInnerPropertyBag Object

boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);

boMainPropertyBag.Add("QE_ServerDescription", "localhost");

boMainPropertyBag.Add("QE_SQLDB", "True");

boMainPropertyBag.Add("SSO Enabled", "False");

//Create a new ConnectionInfo object

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =

new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

//Pass the database properties to a connection info object

boConnectionInfo.Attributes = boMainPropertyBag;

//Set the connection kind

boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

//*EDIT* Set the User Name and Password if required.

boConnectionInfo.UserName = "UserName";

boConnectionInfo.Password = "Password";

//Pass the connection information to the table

boTable.ConnectionInfo = boConnectionInfo;

//Get the Database Tables Collection for your report

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

boTables =

boReportDocument.ReportClientDocument.DatabaseController.Database.Tables;

//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 = "TABLE1";

boTable.QualifiedName = "your_data_base.dbo.TABLE1";

boTable.Alias = "TABLE1";

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables[0],

boTable);

//Verify the database after adding substituting the new table.

//To ensure that the table updates properly when adding Command tables or Stored

Procedures.

boReportDocument.VerifyDatabase();

return boReportDocument;

}

</code>

Former Member · ‎03-10-2010

I am still struggling with a similar issue. Our large base of Crystal reports has been deployed to customers (since Crystal 4) with our own application system which resets the logon information to the customers' own database and user information. Our current deployment uses VS 2008, Crystal 11.5 runtime and CRAXDRT which we now understand is not supported with VS 2008.

We are trying to upgrade to Crystal 2008 and have successfully set logon information for ODBC and OLE DB references but have been unable to do some of the database relocation that we could previously in CRAXDRT. We have some reports that were able to be run both against an Access database or a SQL Server database. Is this still possible in the Crystal 2008 object model? If Inproc RAS is required, is that still freely distributable with the Crystal runtime? Is there any documentation that helps converting CRAXDRT code to the Crystal 2008 ReportDocument model? So far the only missing functionality I have found is the progress bar, but I worry about what's around the next corner in the conversion. Thanks for any help you can give.

Former Member · ‎02-15-2010

I thought it was solved but I have another issue with the subreports.

Within a subreport the following line throws an exception:

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables, boTable);

Message = "Not supported within subreports."

at CrystalDecisions.ReportAppServer.SubreportWrapper.get_IsOpen()

at CrystalDecisions.CrystalReports.Engine.ReportDocument.get_IsLoaded()

at CrystalDecisions.CrystalReports.Engine.ReportDocument.get_ReportClientDocument()

Thank you very much.

Edited by: jporcar on Feb 15, 2010 10:46 AM

Table Location Error Oracle and SqlServer

Accepted Solutions (1)

Accepted Solutions (1)

Server Error in '/CrystalReportsWebSite3' Application.

+No se pudo abrir la conexión.<br />

Answers (7)

Answers (7)

Re: Delete distinct records from an internal table...

Re: generating qrcode using image url in crystal r...

Re: Building custom app at SAP S/4 public cloud

Error: "Instantiation of calculation model failed ...

Re: SAP PI - Determine the receiver based on Sourc...

Table Location Error Oracle and SqlServer

Accepted Solutions (1)

Accepted Solutions (1)

Server Error in &#39;/CrystalReportsWebSite3&#39; Application.

+No se pudo abrir la conexión.<br />

Answers (7)

Answers (7)

Re: Delete distinct records from an internal table...

Re: generating qrcode using image url in crystal r...

Re: Building custom app at SAP S/4 public cloud

Error: "Instantiation of calculation model failed ...

Re: SAP PI - Determine the receiver based on Sourc...

Server Error in '/CrystalReportsWebSite3' Application.