Skip to content

Month: March 2012

How To Get A Comprehensive List of all E-Mail Enabled Libraries in SharePoint

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:

     Webs.FullUrl As LibraryURL,
     AllLists.tp_Title As LibraryTitle,
     AllLists.tp_EmailAlias As emailAlias
     webs on AllLists.tp_WebID = Webs.Id
    AllLists.tp_EmailAlias IS NOT NULL
Opening up SQL Server Management Studio, and running this query on each content database will give you a comprehensive list of all e-mail enabled document libraries.

Upgrading SQL Server Reporting Services to 2012 In SharePoint Integrated Mode

That title could actually be longer….

SQL Server 2012 brings with it a number of key Business Intelligence features that apply directly to SharePoint environments. One of the major improvements is the way that Reporting Services installs. Prior to version 2012, when running in SharePoint integrated mode, it installed along side of SharePoint, and connected through an add-in. With 2012, it is now a full SharePoint Service application, with all of the associated benefits that brings.

It is simple enough to set this up on a new farm, but what about organizations that are already using SSRS in integrated mode? Since I was unable to find any prescriptive guidance on the upgrade process, I ran through it on a test farm, and below are my findings. This describes the process of upgrading from SSRS 2008 R2 to the RTM version of SSRS 2012.

SQL Server 2012 has some relatively strict operating system requirements. First and foremost, you need to be running at least Windows 7 or Windows Server 2008 R2 Service Pack 1, or Windows Vista or Windows Server 2008 Service Pack 2. If not, you’ll get the following message immediately.


In addition, depending on what you’re upgrading, it’s pretty fussy about your source environment as well. For example, if you’re upgrading management tools or BIDS, and you already have Visual Studio 2010 installed, it will need to be at least at the Service Pack 1 level. Your source SQL Server also has specific service pack requirements. The complete supported upgrade matrix can be found here. Unfortunately, if these requirements are not met, the installation will fail much further along in the process, and you’ll need to repeat several steps after correcting.

Once the SQL Server Installation Center launches, you’ll want to pick the Installation tab, and then the Upgrade option.


After a few steps, you’ll encounter one of the new screens pertaining to Reporting Services.


Previously, the installer was totally unaware of Integrated Mode Reporting Services.  You would use SSRS configuration to set it up, but now the upgrade wizard, as well as the full product installer, is fully aware of Integrated Mode.

When performing the upgrade, the installer will go ahead and create the SharePoint service application for you. This is different than when you perform a fresh install – in  that case you manually create the service application after installation. However, in order to do so, it needs to create an application pool for the service application, and you will be prompted for the credentials of that pool.


After several more standard screens, the upgrade rules will be run. This is where you will find out if you are missing a prerequisite, or it is not at the required patch level. However, if all is good, all of the rules should show as Passed, with the exception of “Direct Browsing to Report Server”, which will show a warning.


Previously, if you knew the correct URL, you could navigate directly to Reporting Services and the reports stored within SharePoint through a very rudimentary interface. This warning is simply alerting you to the fact that this is no longer an option with 2012.

The remainder of the installation is straightforward. When done (and if successful), you can navigate to the Service Applications section of Central Administration. There, you should see the new SSRS application.


I’m not a big fan of the name that the default upgrade uses for the application, but that’s simple enough to change. The important thing to note is that all of your subscriptions, snapshots, etc, will have migrated over. The upgrade upgrades the two Reporting Services databases (ReportServer, and ReportServerTempDB by default), and adds a new one, ReportServer_Alerting, which are all used by the service application.


In order to enable data alerts and subscriptions, a number of security modifications need to be made to the SQL Server. In addition, the SQL Server Agent must be running to use these features. Editing the Service Application shows a screen that has a link to Provision Subscriptions an alerts. Clicking through it reveals the following screen:


The View Status section simply gives you an indication of whether or not the Agent is running on the server, but clicking the “Download Script” button will give you a SQL script that will set up the required roles and permissions on your SQL Server. This script must be run on the SQL Server that holds the Reporting Services databases. In order to run it, simply open up SQL Server Management Studio, connect to the server, and click the New Query button. Once the query window opens, paste the query in, and run it (the Execute button).


Finally, enter the credentials for SharePoint to connect to your SQL Server Agent, and click OK. Once done, you’ll be in a position to use the new features available in Reporting Services, and all of your existing investments in reports should continue to operate as before.

For a major architectural change, this is actually a pretty smooth transition.

As I post this, it’s quite early in the life of 2012, so I would be quite interested to hear of any other experiences or gotchas. If there’s something that I should add to this post, please post a comment, I’d love to hear about it.


How To Upgrade SQL Server 2012 RC0 to Release (RTW or RTM)

If you’ve been working with the RC0 version of SQL Server 2012 (formerly code named “Denali”), you are likely aware that the final release is now available as an evaluation download here, or from MSDN with in the Standard, Business Intelligence, and Enterprise editions.

If you want to a straight upgrade from the evaluation edition, it’s a relatively simple process. From the installation screen, select the Upgrade from a previous version of SQL Server option (it doesn’t list RC0, but it is the same process).


Select all of the appropriate options, and your server will be upgrade. However, if you want to move from the RC0 to one of the licensed editions, you could run into trouble. In my testing, what has happened is that parts of the installation will upgrade properly,, but others will fail. This can be seen from the instances screen when upgrade is run for a second time.


Unfortunately, the “left behind” instance can not be upgraded further, and now that this has been run, the Edition upgrade function will fail to work. In one case, I needed to do a complete SQL uninstall and then a reinstall. I’ve had more fun. The good news is that the reinstall did work.

The way to avoid this is to do the Edition upgrade first. If you’re unfamiliar with this, you can find it on the Maintenance tab of the SQL Server installation Center


Once you complete the edition upgrade, go ahead and perform the standard upgrade as you would with the evaluation version.

Leave a Comment