Monday, January 22, 2018

Sitecore WFFM SQL Errors

In a version 8.2 instance of Sitecore with version 8.2 of WFFM (rev. 161129), I was programatically submitting data to a WFFM form. The code would run through with no exceptions, however the form reports never had any data in them. Looking into the log files was showing the following errors:
Exception: System.Exception
Message: Invalid object name 'dbo.Fact_FormSummary'.
Source: Sitecore.Kernel 
Exception: System.Data.DataException
Message: Error executing SQL command: SELECT [FieldId],[dbo].[FormFieldValues].[FieldValueId],[dbo].[FormFieldValues].[FieldValue], [dbo].[Fact_FormSummary].[FieldName], [Count]
  FROM [dbo].[Fact_FormSummary], [dbo].[FormFieldValues]
  WHERE [dbo].[Fact_FormSummary].FieldValueId = [dbo].[FormFieldValues].FieldValueId AND FormId = @FormId 
Exception: System.Exception
Message: Invalid object name 'dbo.Fact_FormStatisticsByContact'.
Source: Sitecore.Kernel 
Exception: System.Data.DataException
Message: Error executing SQL command: SELECT df.Dropouts, df.Failures, df.Submits, df.Success, dv.Visits FROM
(SELECT [dbo].[Fact_FormStatisticsByContact].[FormId] AS FormId, 
SUM([dbo].[Fact_FormStatisticsByContact].[Submits]) AS Submits, SUM([dbo].[Fact_FormStatisticsByContact].[Failures]) AS Failures,
SUM([dbo].[Fact_FormStatisticsByContact].[Dropouts]) AS Dropouts, SUM([dbo].[Fact_FormStatisticsByContact].[Success]) AS Success
FROM [dbo].[Fact_FormStatisticsByContact] WHERE [dbo].[Fact_FormStatisticsByContact].[FormId] = @FormId
group by [dbo].[Fact_FormStatisticsByContact].[FormId]) df INNER JOIN
(SELECT Count(distinct [dbo].[Fact_FormEvents].[InteractionId]) AS Visits,
[dbo].[Fact_FormEvents].[FormId] AS FormId 
  FROM [dbo].[Fact_FormEvents]
  WHERE FormId = @FormId
  GROUP BY [dbo].[Fact_FormEvents].[FormId]  ) dv ON df.FormId = dv.FormId
In this case, the local machine I was developing off was using a base version of the Sitecore reporting database. This meant that the required WFFM install SQL script was not run on the database, hence all of the errors.

Inside the website root is a data folder (C:\inetpub\wwwroot\MyWebsite\Website\Data) which contains a WFFM_Analytics.sql file, running this on the reporting database will resolve the issue. If this file is not present, it can be found inside the install package for your version of WFFM.

No comments:

Post a Comment