I recently completed a multi-farm consolidation and upgrade from SharePoint 2007 to 2010 for a customer, collapsing three farms into one. The approach was to create a new farm, and to individually do dbattach upgrades of each of the 2007 databases into separate SharePoint applications. Everything went well, including Reporting Services subscriptions, but we ran into a problem with incoming email.
The problem is that while all of the incoming email settings for a library are properly migrated when doing a dbattach upgrade, all of the aliases are stored in the Farm Database (in the EmailEnabledLists table, if you’re interested in looking), and it remains empty. The resultant effect is that you inspect the library properties, and all looks good, and email is properly being delivered to the drop folder. Unfortunately, the incoming email service timer job is looking for emails that correspond to the entries in the EMailEnabledLists table, and there are none.
The solution is to simply turn off incoming email for the affected lists, and then turn it back on. A bit cumbersome maybe, but it does work. However, in our case, our customer had over 100 libraries and wasn’t sure where they all were. A bit of hunting around found a couple of ways to get a list of email enabled libraries through code (here and here).
However, I don’t like writing code if I don’t have to, and decided to have a look in the content databases. Don’t forget the first rule of playing around in the SharePoint content databases, which is don’t. However, reading from them isn’t so bad, and ultimately solves our problem. Enough information is in fact in there to construct a simple query:
SELECT Webs.FullUrl As LibraryURL, AllLists.tp_Title As LibraryTitle, AllLists.tp_EmailAlias As emailAlias FROM AllLists INNER JOIN webs on AllLists.tp_WebID = Webs.Id WHERE AllLists.tp_EmailAlias IS NOT NULL
As stated, If you want to find All Incoming E-Mail Enabled Lists and Libraries in SharePoint, using other than SQL Server query method: You can use PowerShell or C# code:
Find the PowerShell Script and C# code here: http://www.sharepointdiary.com/2011/06/find-all-incoming-email-enabled-lists.html#ixzz2aKvhRTDN
What’s up? Exactly what I needed on a moments notice…, thank you!
Hey Ivan – glad to help!
See you shortly….
Thanks a lot, great time saver!
Great idea thanks, but a little bit more complicated if you have lots of content databases, right?
I am going to add sharepoint to file explorer with the help of https://windowsclassroom.com/add-sharepoint-to-file-explorer/ and i think this will solve my problem regarding adding email to sharepoint.
I have to go along with Adam and Thomas Radman. PowerShell can find the same information and create a report in one shot without having to go through each of my 354 content databases. Of course, I could write a PowerShell script to do the SQL queries, but if I have to write a script anyway …