Thursday, June 4, 2015

Sitecore 8 WFFM Data Aggregation Error

Issue : 

WFFM Data not aggregated (transferred) from MongoDB to MSSQL Reporting database

Error :

 8184 18:38:35 ERROR Error during aggregation.
Exception: System.Data.SqlClient.SqlException
Message: T-SQL ERROR 242, SEVERITY 16, STATE 3, PROCEDURE (null), LINE 231, 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,
.....


Sitecore Version :  

8.0 Update-2  & 8.0 Update-3


Solution :


Adding following configuration (if not exists) into /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>          
     

 Run following SQL on Reporting Database

--------------------------------------------------------------------------------------------------------------------
 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
 --------------------------------------------------------------------------------------------------------------------

Note : Thanks Sitecore Support for proving the above Fix.

3 comments:

  1. Could you please provide the reference number? We got update 4 by now and I don't see any info in the release notes that would indicate this bug got fixed…

    ReplyDelete
  2. hi Jan,
    As I remember, I raised the first ticket with sitecore support for update-2 (I think this bug was there from sitecore 8 initial release)
    Then, when I tested this in update-3, the issue was still there.
    When I check with sc support, what they said was this was not yet implemented/fixed in update-3 also. So, I assume this was not fixed in update-4 (latest release) as well (since I applied the patch into update-4 also).
    there is no publish reference no since public reference numbers were introduced recently.
    but, if you contact sitecore support, following is the ticket id for this -> 434923

    ReplyDelete
  3. You can also check the Sitecore KB article about this: https://kb.sitecore.net/articles/282795

    ReplyDelete