Crystal problems with Schemas and Named vs Unnamed SQL instances

[Former Member]

I've used Crystal IDE to develop a report that calls a sproc with a non-default schema. My development used a named SQL instance during the design time.

When the report is deployed, the .NET code passes a connection object with Server Name, Database, User, & password.

When the product is deployed to a system with a named SQL instance the SQL Profiler shows that the statement includes the following:

"databasename"."schemaname"."sproc" (the rest of the statement).

This succeeds and returns the needed data for the report.

If you deploy the product to a system with a default SQL instance, the SQL Profiler shows that the statement includes the following

"sproc" (the rest of the statement)

This latter example fails with at SQL Database Vendor Code of 2812 (sproc not found).

Now, if you rebind the report using the Set Database Location in the Crystal IDE, and use the default SQL instance, the problem is then reversed. I did not trace this last instance change test, but I did confirm that the SQL Database Vendor code of 2812 goes away on the default SQL instance, but now appears on the named SQL instance.

It would appear that the Crystal .NET runtime builds differnent SQL statements depending on database used for the Set Location, and the actual name or unnamed instance used at runtime.

I would like to think that whether the report was developed on the default or named instance of a SQL server should work whether the runtime connection string information uses a named instance or the default instance.

I guess the tough question is how do I get the CR runtime to call SQL Server using

"database"."schema"."sproc" (rest of the statement)

regardless of whether the CR IDE used the named or unnamed instance during the set database location, and regardless of whether the runtime configured to use a named or unnamed instance of SQL Server.

CR2008 Developer Edition (SP3)

VS2010 .NET v4

MS SQL 2008.

Edited by: MarkCONIX on Oct 27, 2010 6:20 PM

[former_member11696 · Accepted solution]

Hi Mark,

Moved to .NET SDK forum

First issue is CR 2008 is not support with 4.0 framework. Build your app using 3.5. It's also not supported in VS 2010. It may work but no escalation path unless you can duplicate the problem in VS 2008.

Next, search using the top right search line on setlocation in this forum or ReplaceConnection and you'll find code samples on how to get, set and build fully qualified SQL to set location.

Thank you

Don

[Former Member · Accepted solution]

Hi Don,

My development implementation and its current cycle will not allow me to back track and try this on prior releases.

What are the chances that you can be sure to add this as a test for the new 2010 release coming next month?

I've reviewed and tried the samples found. They did not solve the problem.

regards,

Mark

[former_member11696 · Accepted solution]

Hi Mark,

Not sure what you are asking? 4.0 will not be supported in CR 2008 and no plans ever to add support.

As for CRVS2010 it works the same way.

Use inProc RAS and then this replace connection method to fully qualify the connection info:

private void ReplaceConnection_Click(object sender, EventArgs e)

{

CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

ISCDReportClientDocument rcd;

rcd = rptClientDoc;

rptClientDoc.DatabaseController.LogonEx("dwcb12003", "xtreme", "sb", "pw");

//Create the logon propertybag for the connection we wish to use

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag logonDetails = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

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

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

logonDetails.Add("Data Source", "dwcb12003");

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

logonDetails.Add("Initial Catalog", "Orders");

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

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

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

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

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

logonDetails.Add("Owner", "dbo"); // schema

//Create the QE (query engine) propertybag with the provider details and logon property bag.

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QE_Details = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

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

QE_Details.Add("QE_DatabaseName", "Orders");

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

QE_Details.Add("QE_LogonProperties", logonDetails);

QE_Details.Add("QE_ServerDescription", "dwcb12003");

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

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

QE_Details.Add("Owner", "dbo");

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;

oldConnInfos = rcd.DatabaseController.GetConnectionInfos(null);

for (int I = 0; I < oldConnInfos.Count; I++)

{

oldConnInfo = oldConnInfos<i>;

newConnInfo.Attributes = QE_Details;

newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

rcd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);

}

}

Thanks

Don