locked
My Sent E-mails View fails after CRM 2011 Upgrade (Generic SQL Error -2147204784) RRS feed

  • Question

  • Hi All,

     

    Following a CRM 4.0 to CRM 2011 upgrade our users are getting a Generic SQL Error whenever they try to open the "My Sent E-mails" view.

    Digging into the trace logs I have found the following error details:

     

    Exception when executing total-record-count query: select 
    COUNT(*) as [#TotalRecordCount] 
    from
     (select 
     DISTINCT  top 5001 "email0".Subject as "subject"
    , "email0".RegardingObjectId as "regardingobjectid"
    , "email0".CreatedOn as "createdon"
    , "email0".ActivityId as "activityid"
    , "email0".to as "to"
    , "email0".RegardingObjectIdYomiName as "regardingobjectidyominame"
    , "email0".RegardingObjectIdName as "regardingobjectidname"
    , "email0".RegardingObjectTypeCode as "regardingobjecttypecode" 
    from
     Email as "email0" (NOLOCK)  join ActivityParty as "a_e505b6046f154d93a8fcca73ccd2f45c" (NOLOCK)  on ("email0".ActivityId  =  "a_e505b6046f154d93a8fcca73ccd2f45c".ActivityId and (("a_e505b6046f154d93a8fcca73ccd2f45c".ParticipationTypeMask = 1 and "a_e505b6046f154d93a8fcca73ccd2f45c".PartyId = '0dd4a69d-5e34-e011-84df-001b78939b60'))) 
    where
     (("email0".StatusCode = 3 and "email0".StateCode = 1 and "email0".DirectionCode = 1))) as #SubQuery Exception: System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'to'.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
       at System.Data.SqlClient.SqlDataReader.get_MetaData()
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
       at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
       at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
       at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
       at Microsoft.Crm.CrmDbConnection.InternalExecuteReader(IDbCommand command, Boolean capturePerfTrace)
       at Microsoft.Crm.CrmDbConnection.ExecuteReader(IDbCommand command, Boolean impersonate, Boolean capturePerfTrace)
       at Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteQuery(CrmDbConnection dbConnection, IDbCommand command, ISqlExecutionContext context)
       at Microsoft.Crm.BusinessEntities.BusinessProcessObject.TryRetrieveExactTotalRecordCountForQuery(EntityExpression query, ExecutionContext context, Int32& totalRecordCount)
    [2011-10-10 16:40:30.821] Process: w3wp |Organization:22d73381-1674-df11-a017-001b78939b60 |Thread:   24 |Category: Exception |User: 326a2009-1774-df11-a017-001b78939b60 |Level: Error | CrmException..ctor
    	at CrmException..ctor(String message, Exception innerException, Int32 errorCode, Boolean isFlowControlException)
    	at SoapExtensionExceptionHandlerBase.PrivateGetCrmExceptionNoDependencies(Exception exception)
    	at SoapExtensionExceptionHandlerBase.GetCrmException(Exception exception)
    	at CrmException.ConvertPluginException(Exception originalException, Boolean isAsync, Boolean unwrapTargetInvocationException)
    	at CrmException.ConvertPluginException(Exception originalException, String pluginTrace, String assemblyName, String pluginType, String stepIdOrWorkflowGroup, String stepDescription, Boolean isAsync, Boolean addPluginTrace, Boolean unwrapTargetInvocationException)
    	at VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)
    	at Pipeline.Execute(PipelineExecutionContext context)
    	at MessageProcessor.Execute(PipelineExecutionContext context)
    	at InternalMessageDispatcher.Execute(PipelineExecutionContext context)
    	at ExternalMessageDispatcher.ExecuteInternal(IInProcessOrganizationServiceFactory serviceFactory, IPlatformMessageDispatcherFactory dispatcherFactory, String messageName, String requestName, Int32 primaryObjectTypeCode, Int32 secondaryObjectTypeCode, ParameterCollection fields, CorrelationToken correlationToken, CallerOriginToken originToken, UserAuth userAuth, Guid callerId, Guid transactionContextId, Int32 invocationSource, Nullable`1 requestId, Version endpointVersion)
    	at OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType)
    	at OrganizationSdkServiceInternal.Execute(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType)
    	at InprocessServiceProxy.ExecuteCore(OrganizationRequest request)
    	at PlatformCommand.XrmExecuteInternal()
    	at RetrieveMultipleCommand.Execute()
    	at RetrieveMultipleCommand.RetrieveData()
    	at ApplicationQuery.ExecuteQuery()
    	at GridDataProviderQueryBuilder.LoadQueryData()
                    ...

     

    There is more to this but this seems to be the relevant section.

    The issue is the query:

    select 
    COUNT(*) as [#TotalRecordCount] 
    from
     (select 
     DISTINCT  top 5001 "email0".Subject as "subject"
    , "email0".RegardingObjectId as "regardingobjectid"
    , "email0".CreatedOn as "createdon"
    , "email0".ActivityId as "activityid"
    , "email0".to as "to"
    , "email0".RegardingObjectIdYomiName as "regardingobjectidyominame"
    , "email0".RegardingObjectIdName as "regardingobjectidname"
    , "email0".RegardingObjectTypeCode as "regardingobjecttypecode" 
    
    from
     Email as "email0" (NOLOCK)  join ActivityParty as "a_e505b6046f154d93a8fcca73ccd2f45c" (NOLOCK)  
    	on ("email0".ActivityId  =  "a_e505b6046f154d93a8fcca73ccd2f45c".ActivityId 
    and 
    	(
    		("a_e505b6046f154d93a8fcca73ccd2f45c".ParticipationTypeMask = 1 
    and 
    		"a_e505b6046f154d93a8fcca73ccd2f45c".PartyId = '0dd4a69d-5e34-e011-84df-001b78939b60')
    	))
    
    where
     (("email0".StatusCode = 3 and "email0".StateCode = 1 and "email0".DirectionCode = 1))) as #SubQuery
    

    The email view (in SQL Server 2008 R2) does not have a "to" column but a ToRecipients column. I can alter the view to add a "to" column but because this is a reserved keyword in TSQL it still won't work. Since this query is generated from a view I cannot change the query to bracket the keyword (, "email0".[to] as "to").

    Removing the to column from "My Sent E-mails" View (in CRM 2011) does not resolve this because this appears to be a generic query that I suppose is run on every view of the E-mail entity to return the total record count.  

    Any idea what can be done to fix this?

     

    Monday, October 10, 2011 4:13 PM

Answers

  • Resolved by Rollup.

    I've updated to Rollup 4 but I don't know which rollup resolved this.


    • Edited by Mark.Cherry Thursday, October 13, 2011 12:49 PM
    • Marked as answer by Mark.Cherry Thursday, October 13, 2011 12:49 PM
    Thursday, October 13, 2011 12:49 PM

All replies

  • (Update: This doesn't always work. It worked on the dev server but not the production server!)

    I have found a temporary workaround for this; essentially you need to create a copy of the view.

    The easiest way to do this is:

    1. From the left hand menu select Settings
    2. From the left hand menu select Customization
    3. Select the Customize the System feature
    4. Expand the Entities group in the Default Solution window
    5. Expand the E-mail entity and select the Views group
    6. Select the My Sent E-mails view and choose More Actions then Edit from the actions menu at the top of the grid
    7. In the view editor window, select Save-As from the top menu
    8. In the Save-As dialog box, change the Name to something unique (i.e. My Sent E-mails 2)
    9. Select the OK button
    10. In the view editor window, select the Save and Close button from the top menu
    11. In the Default Solution window select the My Sent E-mails 2 (or whatever you named it) view and select More Actions then Activate

    NB You can not easily delete the My Sent E-mails view because it is a managed view.


    • Edited by Mark.Cherry Tuesday, October 11, 2011 10:22 AM
    Tuesday, October 11, 2011 8:55 AM
  • Resolved by Rollup.

    I've updated to Rollup 4 but I don't know which rollup resolved this.


    • Edited by Mark.Cherry Thursday, October 13, 2011 12:49 PM
    • Marked as answer by Mark.Cherry Thursday, October 13, 2011 12:49 PM
    Thursday, October 13, 2011 12:49 PM