Contact not adding to Contact Lists in Sitecore 8 Update-2

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:

<Table>Fact_FormStatisticsByContact</Table>

<Routine>Add_FormStatisticsByContact</Routine>

</SqlMappingEntity>

<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.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

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.. :-)

My Sitecore Footsteps

Thursday, April 30, 2015