微软动态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 ,然后就可以了。