Workaround to fix email enabled list no longer receive emails after site migrated from one farm to another

We have identified an issue on email enabled list no longer receiving emails after 2007 to 2010 upgrade in previous blog. Recently during SP1 + June CU upgrade, we found when you migrate the content from one farm to another farm, we have the same issue that the email enabled list no longer receiving emails again!

It’s very common to refresh non-production content Dbs by detaching from product and attaching to non-production. One of the use cases is to get latest data from production farm to QA farm for upgrade testing. The result is all the email enabled list no longer receive emails. This is consider as a bug for content DB detaching and attaching!

After I looked at the SharePoint databases tables, it seems like the issue is the email enabled list setting has entries in configuration database that did not carry over from after content DB detaching from one farm to another. The table is in configuration database table named EmailEnabledLists. Here is the way to confirm this issue.

1.       Detach one content DB from farm 1

2.       Attach the content DB to farm 2 and assume this is new farm

3.       View farm 2 configuration DB table EmailEnabledLists and you will see many entries

4.       View farm 1 configuration DB table EmailEnabledLists and you will see not see any entry

5.       Email enabled list on farm 2 will not receive emails

The configuration database table EmailEnabledLists hold all email enabled entries including email alias name, site collection ID, web ID, List ID, and flag whether it has been deleted. On one dev box, I have two site collections (../harryc, ../harrycc, ../harryc/harryc22) and one sub-site. Each has one email enabled list with email alias as hbxHarry, harryc2, harryc22. You could get the enabled list entries from configuration database table using the following script.

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [Alias]

      ,[SiteId]

      ,[WebId]

      ,[ListId]

      ,[Deleted]

  FROM [sbx_config].[dbo].[EmailEnabledLists]

The result is listed in the below screen shot.

It's very clear that after content DB attached to new farm, the entries from source configuration DB table EmailEnabledLists are not recreated after content DB attached. As a result, we have two woraround to fix this issue.

The first workaround is to copy the EmailEnabledLists table content form source farm to destination farm after content DB attached. This will rebuild the email enabled list to new farm.

The second workaround is similar to what we described before to reset each email enabled list. You could use API to loop through each webapp, each site collection, each site, and each list. Then reset the email alias to original value. After email alias reset, you basically add the entry to new farm configuration DB table EmailEnabledLists. You could verify by querying the table. The following code snippet you could use to reset the email alias.

foreach (SPList list in web.Lists)

{

     if (!string.IsNullOrEmpty(list.EmailAlias))

     {

            //Preserve the Email Alias value in temp variable before deleting the value.

            previousEmailAlias = list.EmailAlias;

            //Reset Email Alias with same value

            list.EmailAlias = previousEmailAlias;

            list.Update();

     //Email Alias will be added into new farm configuration database table EmailEnabledLists

     }

}

You should add try and catch to your code and follow development best practice. Hope this could help you to migrate content with email enabled list to different farm.