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