Category Archives: SQL Server

Sitecore Commerce 8 powered by Microsoft Dynamics (SCpbMD) – Cannot change the publishing status of the channel

1 Reply

Well I have been having a lot of fun setting up Sitecore Commerce 8 powered by Microsoft Dynamics; there are a lot of moving parts to get get the catalog data from Ax, all the way into Sitecore. Not to get political but in fact 90% of the issue have come from Microsoft Ax not being setup correctly.

Routing Service

The Routing Service is responsible for transferring the catalog data from the Channel Database into Sitecore Commerce Server which acts as an edge server for Sitecore. In addition the routing service needs to access the Microsoft Ax Real Time Service (RTS) to change the publishing status.

But why does Sitecore need to update the publishing status anyway? I would assume that was the responsibility Microsoft Ax?

The Sitecore Routing Service is considered part of AX channel publish step. The Routing Service needs to ensure all publishes from HQ to channel database is finished. For a more detailed answer see this article from Microsoft, where the “Routing Service” is equivalent to the SharePoint in the diagram.

I found the following articles by Hao Liu, very useful in tracking down errors and issues with the catalog synchronization.

Communication with Ax is a black box

When communicating with Microsoft Ax it is completely transparent. You call the Microsoft run time DLL’s which connect to the channel database; which resolves how to communicate with the RTS, get the catalog data, create shopping baskets, talk to HQ’s Ax to create orders etc.
Which is great until it does not work as determining where it has gone wrong is a nightmare.
I did not have access to the Microsoft Ax setup and we got  the following exception.

Exception while calling invoke method UpdateChannelPublishingStatus: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultException for the fault code and detail.

Any of the following reasons can cause the a fore mentioned error

  1. You have to use SSL to communicate with the RTS web services
  2. The RTS web service is not running at all.
  3. The RTS web service is not running SSL (which it must)
  4. The RTS web service “host name” does not match the name of the SSL certificate.
  5. The host name running the RTS Web service does not match the host defined in Microsoft Ax.
  6. The credentials running the RTS web service are not correct, and do not have access to the HQ Ax.

Cannot change the publishing status of the channel (record Id: 5637146084) to ‘Failed’.

Then we got the following exception and we (Sitecore Support, The Microsoft Ax Partner & myself) spent a lot of time trying to identify what was causing the following exception:

Cannot change the publishing status of the channel (record Id: 5637146084) to ‘Failed’.
at Microsoft.Dynamics.Commerce.Runtime.TransactionService.TransactionServiceClient.GetResponseFromMethod(String methodName, Object[] parameterList, Boolean useExtensionMethod) at Microsoft.Dynamics.Commerce.Runtime.TransactionService.TransactionServiceClient.UpdateChannelPublishingStatus(Int64 channelId, OnlineChannelPublishStatusType publishingStatus, String publishingStatusMessage)

Solution

I am not sure if it was sheer desperation, divine intervention, or more likely luck!!!

But I checked the SQL server which hosted the channel database, and it had no disk left.

For some reason (I did not install the async client and or the channel database) the recovery mode was set to “Full” and it had therefore create 1 TB of log file 😦

I set the recovery mode to “Simple”, ran shrink on the database and the size dropped from 1TB to almost nothing, and the exception was gone 🙂

SQL Provider (SaveToDatabase Action) for Web Forms For Marketers (WFFM 2.5 – Sitecore 7.5)

24 Replies

With the release for WFFM 2.5 for Sitecore 7.5; SQL server is no longer supported (see release notes) as each time a form is submitted it automatically saves the form data in the xDB (MongoDB) and associates it with the current contact (used to be called visitor).

I believe MongoDB is a much better choice than SQL for storing non-structured data that is generated by WFFM and in addition it can also be used to tailor the experience to the current contact (visitor) i.e. don’t show them the form if they have already filled it out, etc.

Problem

Unfortunately I have a number of customers that want to upgrade to Sitecore 7.5, but due to a number of business and operational reasons are not ready to migrate MongoDB. Therefore they wish to continue storing their form data in the existing SQL database.

Solution

I decided to create a simple SQL provider for WFFM 2.5, that would allow customers to continue saving/retrieving data frm the existing SQL database.

The shared source module WEB FORMS FOR MARKETERS 2.5 – SQL PROVIDER (SAVETODATABASE) is available at Sitecore’s marketplace and the source code is available at GitHub.

This initial version is very simple and provides the following functionality:

  • Save data to a WFFM SQL database (SaveToDatabase Action).
  • Retrieve all submitted data for a given form.
  • Download the data as a CSV file (see separate blog post)
  • Specify a date range to be exported (see article)
  • Define the CSV Delimiter (see article)

It was my intention to port the forms report viewer from a previous version, but the architecture has changed considerably to accommodate xDB and it would have required me to re-code and override a lot of classes. In addition there were a number of UI control libraries that the forms report viewer relies upon which are no longer used/bundled with Sitecore 7.5.

Therefore I intended to implement the ability to down load the data for a given form as an CSV file. So the data can be analysed and sorted using Excel, which in my experience is how the majority of customers used WFFM data.

Installation

Install the WFFM.SQLServer.SaveToDatabase-1.4.zip package, note you prompted that the Save to database item already exists, select overwrite and aply, see below

overwrite

Ensure the WFM.ConnectionString settingin /app_config/include/Sitecore.Forms.config is set to the name of the connection string for the WFFM SQL database i.e.

<setting name="WFM.ConnectionString" value="wfm" />

where the “wfm” connection is defined as follows:

<add name="wfm" connectionString="user id=[USER ID];password=[password];Data Source=[Server];Database=[WFM DATABASE];Connect Timeout=30" />

How To Save Data

Like previous version of WFFM you use the “Save To Database” action to save the submitted form data to the SQL database. The SaveToDatabase action is still /sitecore/system/Modules/Web Forms for Marketers/Settings/Actions/Save Actions/Save to Database. In fact it is the same item from previous versions, so all existing forms that use the SaveToDatabase action will continue to work without making any changes.

save to database item

The only change is that class is WFFM.SQLServer.SaveToDatabase.Infrastructure.Actions.SaveToDatabase, from the WFFM.SQLServer.SaveToDatabase assembly.

Retrieve data for a given Form

To Insert new form data and retrieve data there is a FormReposiotry class which has a Get function to retrieve a list of form data for a given form id, seee below

public IEnumerable<IForm> Get(ID formId)

I have added a button to download data as CSV see this article.

And it is now possible to specify a date range to export and the delimiter used to separate the columns in the CSV file (see article)

Links

marketplace.sitecore.net/Modules/Web_Forms_for_Marketers_SQL_SaveToDatabase.aspx

github.com/TakeitEasyAlan/WFFM-SQL-Server-SaveToDatabase