How to fix SharePoint 2010 email enabled list no longer receive incoming emails issue after upgrade from 2007

SharePoint provides a great feature that a site owner can enable and configure incoming e-mail support for the following list or library: 

• Document, picture, or form library
• Announcements list
• Calendar list
• Discussion board
• Blog

You could refer Microsoft site for more details how you could enable this on SharePoint. After upgrading SharePoint from 2007 to 2010, the first issue we identified is email enabled list no longer receive emails.This has been identified as SharePoint 2010 upgrade bug. After two days research we identified the procedures how to fix it to share.

 To resolve the issue, navigate to the settings for the library and click on the Incoming e-mail settings.

Since the site was upgraded, the current setting should be YES and the email address defined.  Change "Allow this document library to receive e-mail" to No and click Save.  Next, click Incoming E-mail Settings again and set "Allow this document library to receive e-mail" to Yes and click Save again.You could refer one useful blog for details.

Now the real challenge is to identify all the emailed enabled lists so we could fix them immediately. I looked at the SharePoint database and found we could use direct database query to identify those lists. Here is the one we used August 2010 after we upgraded the SharePoint.

SELECT Webs.FullUrl, AllLists.tp_Title, AllLists.tp_EmailAlias

FROM AllLists

Inner join webs on AllLists.tp_WebID = Webs.Id

where AllLists.tp_EmailAlias is not null

You can run this script against each content DB to report all email enabled lists. The formats return will look like as below:

FullURL            Title                                                   eMailAlias 

it/EA/SSAT       Jive Development Process                  jivedevdoc

The URL is not full URL and you need to pre-append the webapp url like http://<servername or webapp alias>/. You could enhance the script to display the full URL so it will be easier to identify the site.

We also worked with one DBA from Microsoft to provide the script to query all content DB and report all email enabled list inside the whole farm. Due to the urgency of the issue, we used the previous script to query each content DB and have not tested the script named All-List-EmailEnabled-2010.sql.

The script assumes that the config database is called SharePoint_Config so if it is named differently please have the DBA update the script. Since this script is much more complex and creates a temporary table in SQL, please have your DBA review it first in case there are any questions. As usual please test in a dev/test environment first. This does make some additions to the SQL instance, it creates a table, or if run multiple times, it will drop the old version of the table and create a new one. Usually for things like this, I recommend doing SQL backups first as anything that touches SQL can always have the potential to cause issues. 

if exists (select name from sysobjects where name = 'sp_tempDBlist2010')
drop procedure sp_tempdblist2010
go
CREATE PROCEDURE sp_tempdblist2010 @ConfigDB varchar(128)
AS
BEGIN
If Exists (select name from sys.objects where name = 'TempDBlist')
EXECUTE ('drop table '+'['+@ConfigDB+']'+'.dbo.TempDbList')
DECLARE @ts1 varchar(1000)
SET @ts1 = 'create table '+'['+@ConfigDB+']'+'.dbo.TempDbList
(
DBname varchar(128),
WebApp varchar(128),
DBInstance varchar(128),
DBServer varchar(128)
)
Insert into '+'['+@ConfigDB+']'+'.dbo.TempDbList

select distinct o.name as ''DBName'',
w.name as ''WebApp'',
b.name as ''DBInstance'',
c.name as ''DBServer''
from SiteMap as s
inner join '+'['+@ConfigDB+']'+'.dbo.Objects as o with (nolock) on s.DatabaseId = o.Id
inner join '+'['+@ConfigDB+']'+'.dbo.Objects as w with (nolock) on s.ApplicationId = w.Id
inner join '+'['+@ConfigDB+']'+'.dbo.Objects as b with (nolock) on o.ParentId = b.Id
inner join '+'['+@ConfigDB+']'+'.dbo.Objects as c with (nolock) on b.ParentId = c.Id
where w.Name not like '''''
EXEC (@ts1)
If Exists (select name from sys.objects where name = 'TempSiteList')
EXECUTE ('drop table '+'['+@ConfigDB+']'+'.dbo.TempSiteList')
DECLARE @ts2 varchar(1000)
SET @ts2 = 'create table '+'['+@ConfigDB+']'+'.dbo.TempSiteList
(
SiteID varchar(260),
SiteURL varchar(260),
EmailAlias varchar(260)
)'
EXEC (@ts2)

DECLARE @eTDBL varchar (400)
DECLARE @eTUCDB varchar(400)
SET @eTDBL = 'DECLARE DB_cursor CURSOR
                FOR
                select DBServer, DBInstance, DBName, WebApp
                from '+'['+@ConfigDB+']'+'.dbo.TempDbList'
EXECUTE (@eTDBL)
OPEN DB_Cursor
DECLARE @vDBServer varchar(128)
DECLARE @vDBInstance varchar(128)
DECLARE @vDBName varchar(128)
DECLARE @vWebApp varchar(128)
FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName, @vWebApp
WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @DBv1 varchar(8000)
    DECLARE @slash varchar(128)
    IF @vDBInstance = ''
        SET @slash = ''
    ELSE
        SET @slash = '\'
    SET @DBv1 = '
Insert into '+'['+@ConfigDB+']'+'.dbo.TempSiteList
select w.SiteID, ''<RootFQDN>''+w.fullurl +''/''+ al.tp_title as SiteURL,
al.tp_emailalias as EmailAlias from ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].dbo.alllists as al with (nolock)
    inner join ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].dbo.webs as w with (NOLOCK) on w.id = al.tp_webid
    where al.tp_emailalias is not NULL
'
    exec (@DBv1)
FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName, @vWebApp
END
CLOSE DB_cursor
DEALLOCATE DB_Cursor

select top 20
o.Name as 'DB Name',
Objects.Name as 'Web App',
SiteUrl,
EmailAlias
from TempSiteList
inner join SiteMap with (nolock) on SiteMap.Id = tempsitelist.SiteId
inner join Objects with (nolock) on Objects.Id = SiteMap.ApplicationId
left join Objects as o with (nolock) on SiteMap.DatabaseId = o.Id
group by o.name, Objects.Name, SiteUrl, EmailAlias
--order by siteurl desc

Drop table TempSiteList
Drop table TempDBList
END
go
sp_tempdblist2010 Sharepoint_Config

The second possible query is to query the configuration database to get the email enabled list. You will need to join the site ID, list ID with other tables in order to get meaningful information. Here is the query.

SELECT *

FROM [EmailEnabledLists]

The result for one item looks like:

Alias                       SiteId                                                                                    WebId                                                                                  ListId                                                                     Deleted

harrycqa              A929944E-BE7A-4BB1-96A7-F2F732070036            C19456BA-AE65-415A-8451-DFA75FFAFAD7         51E50914-9399-4B7F-A3C3-E2258B4015E1 0

There is one more possibility to write a C# program using SharePoint object model to identify email enabled lists and fix them. I just published another blog to further enhance the fix after we upgraded SharePoint 2010 RTM version to SP1 + June CU. You could take a look in another blog.

At this time, you understand the email enabled lists issues during SharePoint upgrade. You have the way to identify all those lists and fix them quickly.