1942763 - Distinct option does not work in Subreport using MDAC OLE DB Provide connecting to MS SQL 2008

SAP Knowledge Base Article - Public

1942763 - Distinct option does not work in Subreport using MDAC OLE DB Provide connecting to MS SQL 2008

Symptom

When using the "Distinct" option in the database connection values are not distinct.

Report is connection to MS SQL 2008 using MDAC OLE DB Provider.

Note: Microsoft has limited support with this driver when connecting to SQL 2008 or above.

Environment

  • Crystal Reports Developer for Visual Studio
  • MS SQL Server 2008
  • MDAC

 

Reproducing the Issue

NOTE: RAS SDK getSqlStatement does not show the Distinct in the SQL Statement:

GroupPath gp = new GroupPath();
string tmp = String.Empty;
try
{
    rptClientDoc.RowsetController.GetSQLStatement(gp, out tmp);
    btnSQLStatement.Text = tmp;
}
catch (Exception ex)
{
    btnSQLStatement.Text = "ERROR: " + ex.Message;
    return;
}

To get the subreport SQL Statement:

// Get subreport SQL
foreach (String resultField in rptClientDoc.SubreportController.GetSubreportNames())
{
    SubreportController subreportController = rptClientDoc.SubreportController;
    SubreportClientDocument subreportClinetDocument = subreportController.GetSubreport(resultField);
    subreportClinetDocument.DatabaseController.LogonEx(crConnectioninfo.ServerName, crConnectioninfo.DatabaseName, crConnectioninfo.UserID, crConnectioninfo.Password);

    try
    {
        subreportClinetDocument.RowsetController.GetSQLStatement(gp, out tmp);
        btnSQLStatement.Text += "\nSubreport: " + resultField.ToString() + "\n:" + tmp;
    }
    catch (Exception ex)
    {
        btnSQLStatement.Text += "\nERROR - Subreport: " + ex.Message;
        return;
    }
}

Resolution

Install MS SQL Server Client tools and use MS Native 10 as the DB client driver.

Also true for MS SQL Server 2013

Note: it has only been reported that this may not work. No details on the report or database other than the results are not valid.

Keywords

CRforVS, MS SQL Server 2008, Distinct, cr2008, cr2010, cr2013 , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports, developer version for Microsoft Visual Studio