Skip to Content
author's profile photo Former Member
Former Member

Crystal problems with Schemas and Named vs Unnamed SQL instances

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Oct 27, 2010 at 07:34 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.