One of our customers recently contacted us with a very annoying issue opening Excel files from SharePoint 2010 document libraries.  Upon arriving to review their installation we discovered the following odd behaviors:

Clicking on a document library link to an Excel document resulted in the following error:

Unable to process the request.
Wait a few minutes and try performing this operation again.

<Research Happens Here>

The Solution

EDIT: Updated solution post service pack 1
(What!!!, you don’t have SP1 installed yet??  checkout this link for details)

1. Navigate: Site Actions –> Site Settings
2. Click the Site Collections Features link located under the Site Collection Administration section of the page
3. Locate and active the Open Documents in Client Applications by Default

NOTE: This does not always resolve the issue, even with SP1 installed.  We have yet to determine a consistent root cause.  If the solution steps above do not resolve the issue, continue on to our original solution below (or reinstall SharePoint… whichever works best for you)

Original Solution
The problem was traced to SharePoint’s built-in URL redirection for various files types.  Behind the scenes, SharePoint was attempting to redirect the Excel file to a common viewer component.  We just needed to turn this redirection off.

Edit the serverfilesExcelServer.xml file at the following location:
[Program Files]Common FilesMicrosoft SharedWeb Server Extensions14TEMPLATEXML

Comment out any lines with the file extension of xlsx or xls

Example:

<?xml version="1.0" encoding="utf-8" ?>

<!– Copyright (c) Microsoft Corporation. All rights reserved. –>

<ServerFiles>
    <!–<Mapping FileExtension="xlsx" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0" NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/> –>
    <Mapping FileExtension="xlsb" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0" NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>
    <Mapping FileExtension="xlsm" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0" NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>
</ServerFiles>

Perform an IISRESET and you will now be presented with the tried and true standard “Open Document” dialog

Setting up SharePoint 2010 Search Service is easy…. if you are running a plain vanilla web application. But, if you are running a more sophisticated setup of SharePoint, you may be in for a rough ride.

In our case, we have a complex setup where we have multiple SharePoint web applications running on a single IIS server, each using HTTPS protocol and host headers.

Overview

• Two SharePoint web applications on a single IIS server using HTTPS.  The HTTPS URL is the only entry point to the web application (I.E. no HTTP or non-SSL support for the applications)
  App1 exists at https://portal.myurl.com
  App2 exists at https://employee.myurl.com
• Both web applications need to support search
• Both web applications are using Basic Authentication to eliminate the need to type the domain name when logging in. (Yes, we can get away with this… we are on an SSL channel after all)
• Both web applications have a super user read account to support the search service

Easy right?  Nope….

Our First Try
Our first attempt at getting search to work followed these steps:

1. Installed the search service from the Central Administration web site using the following navigation:
   Application Management –> Manage Service Applications –> New –> Search Service Application
2. Configured the Search Service Application to taste
3. Add the application URLs to the default content source Local SharePoint Sites
4. Set a crawl rule for https://*.myurl.com with support for basic authentication enabled and the search user/password entered.
   NOTE: This is required to allow the search service to logon to the web application.

And then we executed a full crawl….

And we received two errors:

Access denied to sts4s://portal.myurl.com
Access denied to sts4s://employee.myurl.com

after some digging around, it appears SharePoint search is accessing the web application through a middle layer (the sts4s part).  As we are using Host Headers and basic authentication, the sts4s protocol appears to be ignored by IIS.

Our Second Try
Out second attempt at getting the search to work revolved around extended the web applications to a host header using integrated authentication.  This extended application would be visible to the internal network only

1. Extend the web applications to
   http://portal.myurl.local
   http://employee.myurl.local
2. Remove all of the crawl rules created in our first attempt
3. Reset the default content sources Local SharePoint Sites to use the myurl.local based addresses

And then we executed a full crawl….

No errors! (There was much rejoicing)

And then we actually searched on a keyword we knew existed.  SharePoint’s search returned nothing.  We were receiving the very frustrating “we did not find any results for …” dialog.  (So much for rejoicing)

Our Third Try
We know the crawl worked as we had thousands of Searchable items listed in the search server.  To make the situation even more confusing, the Recent query rate showed the exact number of test queries we had executed.

After more digging, it appears the myurl.local extended applications need a server name mapping defined.  This allows the search engine to know that the HTTPS link is one and the same as the HTTP link.  Here is what we did

1. Create new Server Name Mappings for each URL.
   Example:
   Address for indexing would be http://portal.myurl.local
   Address for display in search results would be https://portal.myurl.com

And then we executed a full crawl…

And everything worked!

The upgrade process from SharePoint 2007 to 2010 is relatively straightforward and painless… unless you encounter a problem during the upgrade process.

In most cases, the Test-SpContentDatabase cmdlet will notify you early in the process of potential gotchas and traps.  In addition, the Test-SpContentDatabase cmdlet even provides a verbose level of feedback and recommendations when issues are encountered.  Pretty sweet SharePoint team!

But, what happens when you receive an error while executing the
Mount-SpContentDatabase command.  Say, something like this:

Mount-SPContentDatabase : Action 4.0.32.0 of Microsoft.SharePoint.Upgrade.SPContentDatabaseSequence failed.
At Line:1 char:24
+ Mount-SPContentDatabase <<<< –Name “My DB Name” –WebApplication “My URL”
+ CategoryInfo : InvalidData: (Microsoft.Share…ContentDatabase: SPCmdletMountContentDatabase) [Mount-SPContentDatabase], SPUpgradeException
+ FullyQualifiedErrorID : Microsoft.SharePOint.PowerShell.SPCmdletMountContentDatabase

While appearing very verbose, this error is not helpful and rather intimidating.

Now what?

Log files, that’s what!

The upgrade process within SharePoint 2010 produces very verbose (and helpful) log files during the upgrade.  These logs are located at

C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14Logs

Find the log file ending with “-error” and review the contents for more details on what occurred during the actual upgrade.  In the case of the error above, the following information was logged

[powershell] [SPContentDatabaseSequence] [ERROR] [7/11/2010 6:26:57 PM]: Exception: Cannot find the object "dbo.AuditData" because it does not exist or you do not have permissions.

Now we have something we can dig into and research!

Root cause for our failure

In this case, the content database was originally upgraded from SharePoint 2003 to SharePoint 2007.  Somewhere in the migration process (and the intervening years), the dbo.AuditData table never got created.

To correct, we simply scripted the dbo.AuditData table from a existing (and functioning) SharePoint 2010 content database and executed those scripts against the failed content database.

Hopefully, your upgrade and migration issues are as easy to fix!