We faced another issue with our Sitecore 8 update-2 upgrade process.
This time, when a user submit a WFFM form, "Add contact to contact list" WFFM save action does not add the user to the relevant contact list.
Error 1:
When we submit the form from front-end, user was not added to the Contact List. Following error was in the error logs.
19576 13:25:26 ERROR Error during aggregation.
Exception: System.Data.SqlClient.SqlException
Message: T-SQL ERROR 242, SEVERITY 16, STATE 3, PROCEDURE (null), LINE 219, MESSAGE: The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range value.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Sitecore.Data.DataProviders.Sql.DataProviderTransaction.Dispose()
at Sitecore.Analytics.Aggregation.SqlReportingStorageProvider.<>c__DisplayClass5.<Store>b__2()
at Sitecore.Data.DataProviders.NullRetryer.ExecuteNoResult(Action action, Action recover)
at Sitecore.Analytics.Aggregation.SqlReportingStorageProvider.Store(Guid id, AggregationDataSet data)
at Sitecore.ExperienceAnalytics.Client.Platform.SwitchingReportingStorageProvider.Store(Guid id, AggregationDataSet data)
at Sitecore.Analytics.Aggregation.InteractionAggregator.Aggregate(Byte[] recordKey, IAggregationContext context)
at Sitecore.Analytics.Aggregation.Aggregator.Execute()
Solution 1:
So, we contacted sitecore support and they provide us with following fix. (ref: 434923)
* Add the highlighted lines to the /App_Config/Include/Sitecore.Analytics.Processing.Aggregation.config file:
<!-- Facts -->
<SqlMappingEntity type="Sitecore.Analytics.Aggregation.SqlMappingEntity, Sitecore.Analytics.Sql">
<Table>Fact_FormStatisticsByContact</Table>
<Routine>Add_FormStatisticsByContact</Routine>
</SqlMappingEntity>
<SqlMappingEntity type="Sitecore.Analytics.Aggregation.SqlMappingEntity, Sitecore.Analytics.Sql">
<Table>Fact_Conversions</Table>
<Routine>Add_Conversions</Routine>
</SqlMappingEntity>
* Rebuild the reporting database
Error 2 :
After that fix, when submit the wffm form, we received following error on logs
1104 13:35:55 ERROR Error during aggregation.
Exception: System.Data.SqlClient.SqlException
Message: Could not find stored procedure 'Add_FormStatisticsByContact'.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Sitecore.Data.DataProviders.Sql.DataProviderTransaction.Dispose()
at Sitecore.Analytics.Aggregation.SqlReportingStorageProvider.<>c__DisplayClass5.<Store>b__2()
at Sitecore.Data.DataProviders.NullRetryer.ExecuteNoResult(Action action, Action recover)
at Sitecore.Analytics.Aggregation.SqlReportingStorageProvider.Store(Guid id, AggregationDataSet data)
at Sitecore.ExperienceAnalytics.Client.Platform.SwitchingReportingStorageProvider.Store(Guid id, AggregationDataSet data)
at Sitecore.Analytics.Aggregation.InteractionAggregator.Aggregate(Byte[] recordKey, IAggregationContext context)
at Sitecore.Analytics.Aggregation.Aggregator.Execute()
Solution 2:
again, sitecore support came to our help and provided us with an sql and asked to run it on "reporting" database (ref : 434923)
CREATE PROCEDURE [dbo].[Add_FormStatisticsByContact]
@ContactId [uniqueidentifier],
@FormId [uniqueidentifier],
@LastInteractionDate [datetime],
@Submits [int],
@Success [int],
@Dropouts [int],
@Failures [int],
@Visits [int],
@Value [int],
@FinalResult [int]
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
MERGE [dbo].[Fact_FormStatisticsByContact] AS t
USING
(
VALUES
(
@ContactId,
@FormId,
@LastInteractionDate,
@Submits,
@Success,
@Dropouts,
@Failures,
@Visits,
@Value,
@FinalResult
)
)
as s
(
[ContactId],
[FormId],
[LastInteractionDate],
[Submits],
[Success],
[Dropouts],
[Failures],
[Visits],
[Value],
[FinalResult]
)
ON
(
t.[ContactId] = s.[ContactId] AND
t.[FormId] = s.[FormId]
)
WHEN MATCHED and (t.[LastInteractionDate] < s.[LastInteractionDate]) THEN UPDATE SET
t.[LastInteractionDate] = s.[LastInteractionDate],
t.[Submits] = s.[Submits],
t.[Success] = s.[Success],
t.[Dropouts] = s.[Dropouts],
t.[Failures] = s.[Failures],
t.[Visits] = s.[Visits],
t.[Value] = s.[Value],
t.[FinalResult] = s.[FinalResult]
WHEN NOT MATCHED THEN
INSERT(
[ContactId],
[FormId],
[LastInteractionDate],
[Submits],
[Success],
[Dropouts],
[Failures],
[Visits],
[Value],
[FinalResult]
)
VALUES(
s.[ContactId],
s.[FormId],
s.[LastInteractionDate],
s.[Submits],
s.[Success],
s.[Dropouts],
s.[Failures],
s.[Visits],
s.[Value],
s.[FinalResult]
);
END TRY
BEGIN CATCH
DECLARE @error_number INTEGER = ERROR_NUMBER();
DECLARE @error_severity INTEGER = ERROR_SEVERITY();
DECLARE @error_state INTEGER = ERROR_STATE();
DECLARE @error_message NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @error_procedure SYSNAME = ERROR_PROCEDURE();
DECLARE @error_line INTEGER = ERROR_LINE();
RAISERROR( N'T-SQL ERROR %d, SEVERITY %d, STATE %d, PROCEDURE %s, LINE %d, MESSAGE: %s', @error_severity, 1, @error_number, @error_severity, @error_state, @error_procedure, @error_line, @error_message ) WITH NOWAIT;
END CATCH;
END;
GO
Error 3:
After the above fix, errors went off from the logs. But, still contacts are not added to contact lists.
Solution 3:
So, finally, a fix will be provided for this issue.
But, solution will be coming with the Sitecore 8 Update-3 and related WFFM 8.0
So, be patient.. :-)
