微软动态CRM专家罗勇 ，回复300或者20190120可方便获取本文，同时可以在第一间得到我发布的最新博文信息，follow me！我的网站是 www.luoyong.me 。
安装好Dynamics 365 Customer Engagement V9 并配置IFD后发现活动源功能有问题，如下报错，咋回事呢？
我跟踪了一下，是执行下面这个FetchXml报错Generic SQL error，从字符串转换日期和/或时间时，转换失败。
<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'> <entity name='msdyn_wallsavedqueryusersettings'> <attribute name='msdyn_wallsavedqueryusersettingsid' /> <attribute name='msdyn_wallsavedqueryid' /> <attribute name='msdyn_savedqueryname' /> <attribute name='msdyn_entityname' /> <attribute name='msdyn_otc' /> <attribute name='msdyn_entitydisplayname' /> <attribute name='msdyn_isvisible' /> <attribute name='msdyn_isvirtual' /> <attribute name='msdyn_default' /> <filter type='and'> <condition attribute='msdyn_userid' operator='eq-userid' /> <condition attribute='msdyn_isvirtual' operator='eq' value='1' /> <condition attribute='msdyn_isvisible' operator='eq' value='1' /> <condition attribute='msdyn_isfollowing' operator='eq' value='1' /> <condition attribute='msdyn_includewallinresponse' operator='eq' value='1' /> </filter> </entity> </fetch>
具体错误信息如下：
>Crm Exception: Message: Generic SQL error., ErrorCode: -2147204784, InnerException: System.Data.SqlClient.SqlException (0x80131904): 从字符串转换日期和/或时间时，转换失败。
在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
在 System.Data.SqlClient.SqlDataReader.get_MetaData()
在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
在 Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
在 Microsoft.Crm.CrmDbConnection.<>c__DisplayClass104_0.<InternalExecuteReader>b__0()
在 Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func)
在 Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)
在 Microsoft.Crm.CrmDbConnection.InternalExecuteReader(IDbCommand command, Nullable`1 commandBehavior, Int32 sourceLineNumber, String memberName, String sourceFilePath)
在 Microsoft.Crm.CrmDbConnection.ExecuteReader(IDbCommand command, Boolean impersonate, Int32 sourceLineNumber, String memberName, String sourceFilePath)
在 Microsoft.Crm.Platform.Server.DataEngine.SqlDataAccessManager.<>c__DisplayClass1_0.<ExecuteQuery>b__0()
在 Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func)
在 Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)
在 Microsoft.Crm.Platform.Server.DataEngine.SqlDataAccessManager.ExecuteQuery(CrmDbConnection dbConnection, IDbCommand command, ISqlExecutionContext context, Nullable`1 commandTimeout)
在 Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteQuery(CrmDbConnection dbConnection, IDbCommand command, ExecutionContext context)
在 Microsoft.Crm.Common.ObjectModel.PostService.PopulatePostCollection(CrmDbConnection sqlconn, ExecutionContext context, OrderedDictionary posts, IDbCommand command, CounterList populatePost, Int32 commentsPerPost)
在 Microsoft.Crm.Common.ObjectModel.PostService.RetrievePosts(Guid entityId, Int32 entityType, Boolean includeFollowing, Int32 pageNumber, Int32 pageSize, Int32 commentsPerPost, DateTime startDate, DateTime endDate, OptionSetValue type, OptionSetValue source, ExecutionContext context, CounterList retrievePostCounterList, Boolean sortDirection)
在 Microsoft.Crm.Common.ObjectModel.PostService.RetrievePersonalWall(Int32 pageNumber, Int32 pageSize, Int32 commentsPerPost, DateTime startDate, DateTime endDate, OptionSetValue type, OptionSetValue source, ExecutionContext context, Boolean sortDirection)
ClientConnectionId:7d3ba9cf-74cf-4755-986c-8c1e14daa019
Error Number:241,State:1,Class:16
[2019-01-20 12:20:22.486] Process: w3wp |Organization:baef5a31-596a-4c7c-a403-e62abe40e04c |Thread: 25 |Category: Platform.Sdk |User: 7475738e-0cca-4e09-bf17-e163fcf1cdcd |Level: Error |ReqId: ea978444-0c29-4067-896a-f5cb421f7f1b |ActivityId: 2117c671-4e6f-4e88-8f56-ccc3e33cae64 | VersionedPluginProxyStepBase.Execute ilOffset = 0x7
at VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context) ilOffset = 0x7
at PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context) ilOffset = 0x83
at <>c__DisplayClass2_1.<Execute>b__0() ilOffset = 0x84
at ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action) ilOffset = 0x49
at XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action) ilOffset = 0x12
at Pipeline.Execute(PipelineExecutionContext context) ilOffset = 0x127
at PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context) ilOffset = 0x32
at MessageProcessor.Execute(PipelineExecutionContext context) ilOffset = 0x237
at InternalMessageDispatcher.Execute(PipelineExecutionContext context) ilOffset = 0x104
at ExternalMessageDispatcher.ExecuteInternal(IInProcessOrganizationServiceFactory serviceFactory, IPlatformMessageDispatcherFactory dispatcherFactory, Guid callerRegardingObjectId, Int32 invocationSource, Version endpointVersion, OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest) ilOffset = 0x33D
at OrganizationSdkServiceInternal.DispatchRequest(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest, SdkResponseDescription responseDescription, SdkPerformanceCounterContext sdkPerformanceCounterContext) ilOffset = 0x6
at OrganizationSdkServiceInternal.ExecuteRequestRequestWithInstrumentation(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest) ilOffset = 0xCD
at <>c__DisplayClass17_0.<ExecuteRequest>b__0() ilOffset = 0xAA
at ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Func`1 func) ilOffset = 0x43
at XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Func`1 func) ilOffset = 0x0
at OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, UserAuth userAuth, Guid targetUserId, Guid targetCallerRegardingObjectId, UserType targetUserType, OrganizationContext context, Boolean returnResponse, Boolean checkAdminMode) ilOffset = 0x11E
at OrganizationSdkServiceInternal.ExecuteRequestInternal(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext) ilOffset = 0x7F
at OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext) ilOffset = 0x60
at OrganizationSdkServiceInternal.Execute(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext) ilOffset = 0x1B
at InprocessServiceProxy.ExecuteCore(OrganizationRequest request) ilOffset = 0x20
at WallSavedQueryUserSettings.IncludeWallInResponse(PluginContext context, Guid userId, IEnumerable`1 wallSavedQueryUserSettings) ilOffset = 0x108
at WallSavedQueryUserSettings.ExecutePostRetrieveMultple(PluginContext context) ilOffset = 0x5DD
at PluginBase`1.Execute(IServiceProvider serviceProvider) ilOffset = 0x84
at V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context) ilOffset = 0x301
at VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context) ilOffset = 0x7
at PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context) ilOffset = 0x83
at <>c__DisplayClass2_1.<Execute>b__0() ilOffset = 0x84
at ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action) ilOffset = 0x49
at XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action) ilOffset = 0x12
at Pipeline.Execute(PipelineExecutionContext context) ilOffset = 0x127
at PipelineInstrumentationHelper.Execute(Boolean instrumentationEnabled, String stopwatchName, ExecuteWithInstrumentation action, PipelineExecutionContext context) ilOffset = 0x32
at MessageProcessor.Execute(PipelineExecutionContext context) ilOffset = 0x249
at InternalMessageDispatcher.Execute(PipelineExecutionContext context) ilOffset = 0x104
at ExternalMessageDispatcher.ExecuteInternal(IInProcessOrganizationServiceFactory serviceFactory, IPlatformMessageDispatcherFactory dispatcherFactory, Guid callerRegardingObjectId, Int32 invocationSource, Version endpointVersion, OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest) ilOffset = 0x33D
at OrganizationSdkServiceInternal.DispatchRequest(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest, SdkResponseDescription responseDescription, SdkPerformanceCounterContext sdkPerformanceCounterContext) ilOffset = 0x6
at OrganizationSdkServiceInternal.ExecuteRequestRequestWithInstrumentation(OrganizationRequest request, OrganizationInternalRequest organizationInternalRequest) ilOffset = 0xCD
at <>c__DisplayClass17_0.<ExecuteRequest>b__0() ilOffset = 0xAA
at ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Func`1 func) ilOffset = 0x43
at XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Func`1 func) ilOffset = 0x0
at OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, UserAuth userAuth, Guid targetUserId, Guid targetCallerRegardingObjectId, UserType targetUserType, OrganizationContext context, Boolean returnResponse, Boolean checkAdminMode) ilOffset = 0x11E
at OrganizationSdkServiceInternal.ExecuteRequestInternal(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext) ilOffset = 0x7F
at OrganizationSdkServiceInternal.ExecuteRequest(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext) ilOffset = 0x60
at OrganizationSdkServiceInternal.Execute(OrganizationRequest request, CorrelationToken correlationToken, CallerOriginToken callerOriginToken, WebServiceType serviceType, Boolean checkAdminMode, ExecutionContext executionContext) ilOffset = 0x1B
at OrganizationSdkService.Execute(OrganizationRequest request) ilOffset = 0x0
at ilOffset = 0xFFFFFFFF
at SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) ilOffset = 0x222
at DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) ilOffset = 0x97
at ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) ilOffset = 0x48
at MessageRpc.Process(Boolean isOperationContextSet) ilOffset = 0x65
at Wrapper.Resume(Boolean& alreadyResumedNoLock) ilOffset = 0x1B
at ThreadBehavior.ResumeProcessing(IResumeMessageRpc resume) ilOffset = 0x8
at ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) ilOffset = 0x79
at ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx) ilOffset = 0x9
at QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem() ilOffset = 0x35
at ThreadPoolWorkQueue.Dispatch() ilOffset = 0xA4
>Web Service Plug-in failed in SdkMessageProcessingStepId: {DF5B3FC1-79DB-11E0-A0F5-1CC1DE634CFE}; EntityName: none; Stage: 30; MessageName: RetrievePersonalWall; AssemblyName: Microsoft.Crm.Extensibility.InternalOperationPlugin, Microsoft.Crm.ObjectModel, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35; ClassName: Microsoft.Crm.Extensibility.InternalOperationPlugin; Exception: Unhandled Exception: System.Reflection.TargetInvocationException: 调用的目标发生了异常。
在 System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
在 System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
在 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
在 System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values)
在 Microsoft.Crm.Extensibility.InternalOperationPlugin.Execute(IServiceProvider serviceProvider)
在 Microsoft.Crm.Extensibility.V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context)
在 Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context)
Inner Exception: System.Data.SqlClient.SqlException: 从字符串转换日期和/或时间时，转换失败。
在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
在 System.Data.SqlClient.SqlDataReader.get_MetaData()
在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
在 Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetry[TResult](Func`1 ExecuteMethod, IDbCommand command)
在 Microsoft.Crm.CrmDbConnection.<>c__DisplayClass104_0.<InternalExecuteReader>b__0()
在 Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func)
在 Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)
在 Microsoft.Crm.CrmDbConnection.InternalExecuteReader(IDbCommand command, Nullable`1 commandBehavior, Int32 sourceLineNumber, String memberName, String sourceFilePath)
在 Microsoft.Crm.CrmDbConnection.ExecuteReader(IDbCommand command, Boolean impersonate, Int32 sourceLineNumber, String memberName, String sourceFilePath)
在 Microsoft.Crm.Platform.Server.DataEngine.SqlDataAccessManager.<>c__DisplayClass1_0.<ExecuteQuery>b__0()
在 Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func)
在 Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)
在 Microsoft.Crm.Platform.Server.DataEngine.SqlDataAccessManager.ExecuteQuery(CrmDbConnection dbConnection, IDbCommand command, ISqlExecutionContext context, Nullable`1 commandTimeout)
在 Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteQuery(CrmDbConnection dbConnection, IDbCommand command, ExecutionContext context)
在 Microsoft.Crm.Common.ObjectModel.PostService.PopulatePostCollection(CrmDbConnection sqlconn, ExecutionContext context, OrderedDictionary posts, IDbCommand command, CounterList populatePost, Int32 commentsPerPost)
在 Microsoft.Crm.Common.ObjectModel.PostService.RetrievePosts(Guid entityId, Int32 entityType, Boolean includeFollowing, Int32 pageNumber, Int32 pageSize, Int32 commentsPerPost, DateTime startDate, DateTime endDate, OptionSetValue type, OptionSetValue source, ExecutionContext context, CounterList retrievePostCounterList, Boolean sortDirection)
在 Microsoft.Crm.Common.ObjectModel.PostService.RetrievePersonalWall(Int32 pageNumber, Int32 pageSize, Int32 commentsPerPost, DateTime startDate, DateTime endDate, OptionSetValue type, OptionSetValue source, ExecutionContext context, Boolean sortDirection)
然后我跟踪了一下，发现是一个存储过程 p_RetrievePosts 执行报错，传递执行的是类似下面的SQL
exec p_RetrievePosts @entityId='1400DBCC-3F0B-E911-B0BF-E25A6C657829', @entityTypeCode=8, @includeFollowing=1, @currentUserId='1400DBCC-3F0B-E911-B0BF-E25A6C657829', @sortDirection=0, @type=-1, @source=-1, @pageNumber=1, @pageSize=20, @commentsPerPost=2, @startDate=default, @endDate=default
观察下这个存储过程的代码如下：
可以看到，起始日期和结束日期如果不传递的话，就会用默认值，但是这个默认值对于不同的数据库而言，可能会转换成日期时间会失败。
找到这个就很简单了，可以解决的。更改下这个存储过程，将startDate参数的默认值改成 1900-01-01，endDate参数的默认值改成 9999-12-31 ，然后就可以了。