FileNotFoundException When Creating PowerPivot Service Application

I was called into a client this morning whom had their SharePoint environment built by a separate SharePoint consultant (for shame!). When attempting to create a PowerPivot Service Application, they were encountering the following error:

System.IO.FileNotFoundException: Could not find file ‘C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\Template\sql\PowerPivotDbSchema.sql’.  

This error would not surface through the Web UI, however can be found in the SharePoint ULS logs. Seems easy enough to fix right? Looking under the path specified in the above however, you might not even see the parent Template directory!

Off the cuff, I assumed that PowerPivot simply was not installed. However this was not the case, rather the installation looked like it was right in specs.

Not going into the dull specifics off the error, the problem is with SQL Server 2012 SP1 (slipstreamed version) and PowerPivot. This error doesn’t appear to occur when you are not using the slipstream version. If it is on the menu, this repair could be used. However since this is a freakin cannon at a mosquito, here is a way to retrofit the environment and get around the error:

  1. Logon to the SharePoint machine. Repeat these steps for every box that participates in the SharePoint farm.
  2. If you haven’t installed PowerPivot, follow the steps outlined here:
  3. If you have installed PowerPivot, Open Add/Remove Programs. (Click Start. Click Settings. Click Control Panel. Click Add/Remove Programs.)
  4. Right click and repair the PowerPivot installation.
  5. Open the following location:  C:\Program Files\Microsoft SQL Server\110\Tools\PowerPivotTools\SPAddinConfiguration\Resources\
  6. Find PowerPivotFarmSolution.wsp. Rename this file extension to .cab. Extract the contents.
  7. Find the PowerPivotDbLogic.sql and PowerPivotDbSchema.sql files. Copy them to a familiar location.
  8. Copy the PowerPivotDbLogic.sql and PowerPivotDbSchema.sql files to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\TEMPLATE\SQL. Ad-hoc create the appropriate folder structure as required.
  9. Retry to create the PowerPivot Service Application from Central Admin. Should work.