Reports generated by the CRM Report Wizard fails to run after being modified a couple of times.
The SQL Server Reporting Services log reads:
An exception has occurred in data set ‘DSMain’.
Cannot read the next data row for the dataset DSMain.
Number of link entities in query exceeded maximum limit.
Duplicate link-entity lines have been inserted in the query of the RDL file by the Wizard.
This is a bug in Microsoft Dynamics CRM 2011 Rollup 16. MS Support is on the case, hoping for a hot-fix soon. -UPDATE- This issue will be fixed in Rollup 17 due to be released by late April. – UPDATE-
You can temporarily extend the number of link entities in query exceeded maximum limit by using this unsupported method:
INSERT INTO DeploymentProperties(Id,ColumnName,IntColumn,Encrypted)
select * from DeploymentProperties where ColumnName=’QueryLinkEntityLimit’
Thanks to: http://www.comunidadcrm.com/2012/09/28/fetchxml-error-crm-2011-number-of-link-entities-in-query-exceeded-maximum-limit/
SYMPTOMS: Custom reports do not work with an SSRS log error similar to:
System.ServiceModel.Security.SecurityNegotiationException: A call to SSPI failed, see inner exception.
System.Security.Authentication.AuthenticationException: A call to SSPI failed, see inner exception.
System.ComponentModel.Win32Exception: The target principal name is incorrect —>
PROBLEM: This happens if the SPNs of the CRM web service is not set properly and SSRS server is unable to get a Service Ticket from the Ticket Granting Server (TGS).
SOLUTION: Register the following SPNs:
setspn -a http/<CrmApplicationServerName> <CrmAppPoolServiceUserAccount> (E.g. setspn -a http/crm DOMAIN\SVCCRM)
setspn -a http/<CrmApplicationServer FQDN> <CrmAppPoolServiceUserAccount> (E.g. setspn -a http/crm.domain.com DOMAIN\SVCCRM)
Say you have seconds stored in the database, but you want to show them as hours and minuts. Here’s a simple SQL Server Reporting Services expression you can use:
=Int(Sum(Fields!DurationSec.Value) / 60) & “:” & Right(“0” & (Sum(Fields!DurationSec.Value) – (Int(Sum(Fields!DurationSec.Value) / 60) * 60)), 2)