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.
