Excel Services error - This workbook is larger than the maximum workbook size allowed to be opened in the browser

In scenarios, where you need to develop quite large Excel PowerPivot files and deploy to SharePoint, there is are two interesting steps to be performed.

Step 1: You need to enable the ReporServer to allow doing so by adding a parameter

“maxRequestLength=100000” to the “httpRuntime” in the web.config file for Reporting Services.

This web.config file usually resides in a location similar to “C:\Program Files\Microsoft SQL Server\MSRS10_50.SQLEXPRESS\Reporting Services\ReportServer”. (Your drive letter or the path may differ based upon your install).

Once set, do an IISREST.

When this parameter is not set in the web.config, you might see an error like:

“There was an exception running the extensions specified in the config file. ---> Maximum request length exceeded.”

Step 2: In addition to Step 1, you also need to raise the default file size limit on Excel Services on SharePoint, else you get an error:

Cause: OOB, the maximum workbook upload size allowed in SharePoint 2010 is 10mb.

To get rid of the above error, perform the below steps:

a. Go to Central Administration => Application Management =>Manage service applications.

b. Click Excel Services Application (click the parent, not the child proxy).

c. Click Trusted File Locations.

d. Click the location. This will be an added trusted file location for the site collection or a new file location, usually like “https://”.

e. Scroll to Workbook Properties.

f. In Maximum Workbook Size, increase the file size from 10 to 50.

Click OK. Refresh your report page to see it load without any errors.

Setting up SharePoint 2013 Development Environment – Part 2

This is the Part 2 of the series for setting up a development environment for SP 2013 on a VM. As we know, this is the latest release for the new Microsoft stack of products that involves Windows Server 2012, SQL Server 2012, Visual Studio 2012 and SharePoint Server 2013 (or SP 15).

In this part 2, we will focus on Installing SQL Server 2012 on top of Windows Server 2012 that we installed on our VM.

- Start the Setup wizard for SQL Server 2012. Click on the “New stand-alone install…”.

Once you go through the steps of Updates for SQL Server, Setup Support Rules, License Keys and Accept Terms, you’ll need to choose from 3 different Setup Roles. I chose SQL Server Feature Installation.

On the next screen, Click [Select All].

Click Next. It should pass all the 3 Installation Rules as below:

Choose “Named Instance” and provide a name. Press TAB, this should update the Instance ID and all the 3 directory paths. Click Next.

Click Next on the Disk Usage Summary.

Specify Service Accounts as created earlier for SSRS and SSAS. I’ll leave others as is (as a best practice, in prod environments, you may want to specify the service accounts for others as well).

Click Next.

Leave the Auth Mode = “Windows” and click [Add Current User]. Click Next.

Click Next. Then Click [Add Current User] to specify which user has permissions for the distributed replay controller. Click Next.

Specify controller name and leave the directory locations as is. Click Next.

Click Next. Then Click Next again on Error Reporting. It should be something like:

Click Next and then Install. Wait for the setup to complete.

This completes the SQL Server installation with SSRS (SQL Server Reporting Services) configuration in Native mode only.

At this point of time, let’s take another snapshot of the VM and do a windows restart.

So, that’s it for getting the basic SQL Server 2012 installed and running.

We will move to the Part 3 of this series, where we will get the SSAS with Tabular and PowerPivot for SharePoint Integration modes.