SQL Server – Page 5 – The White Pages

Reporting Services Web Part Won’t Save Parameters with SharePoint 2013 SP1

Published September 16, 2014

We recently performed an installation of SharePoint 2013 with SP1 for a customer using the slipstreamed ISO available on MSDN. Part of the rollout in this case involved migrating a number of reports from a standalone instance of SQL Server Reporting Services (SSRS) to SSRS running in SharePoint integrated mode. However, during the creation of some new reports, we observed something odd. The custom parameter values were not being saved for web parts that were using these reports.

SSRS web parts in SharePoint allow for a report to be displayed within a web part, a therefore in part of the page. Report parameters created in the report can be exposed as web part properties. Therefore, you can have multiple instances of the same report on different pages using different parameters. You can also connect the web part to other web parts, such as SharePoint filters, and have the parameters driven by these filter values. However, in this particular case, none of it was working. The parameter could be saved, and applied, but when the page was saved, the parameter values would revert to their defaults.

After doing some digging, I came across this discussion on the MSDN forums. The discussion indicated that there may be a regression bug in SharePoint 2013 Service Pack 1 that exhibited this behaviour, and that the application of the May 2014 Cumulative Update had helped them somewhat. We had never seen this issue with builds prior to SP1. Given that our farm had not yet gone live, we quickly acquired and applied the July 2014 CU in the hopes that it would help.

It did.

In our case, it fixed the problem completely, however there was one small negative side effect. All of the web parts that had been added to pages before the update completely lost all of their saved properties, including the report that they were pointing to. In our case, this was no big deal, but if you have a lot of these, it could be an annoyance. I also don’t know if this would happen generally with the July 2014 CU, or it was only because that the original system was affected (as I suspect).

In any event, this apparently was a regression bug introduced with SP1, and applying the July 2014 CU clearly fixed this problem for us. Our new baseline for SharePoint 2013 will not be sP1, but SP1 with the July 2014 CU.

Problems Installing PowerPivot for SharePoint and Reporting Services From SQL Server 2012 SP2 on SharePoint 2013

Published July 20, 2014

I recently performed an installation of both PowerPivot and Reporting services for a customer that was running SharePoint 2013. In preparation for the installation, the customer had made the SQL media available to us (something that I always appreciate), so installation looked to be quick and easy. Since SP2 was released for SQL Server 2012 over a month ago (June 2014), they had downloaded the ISO file from MSDN that already included SP2.

I went ahead and installed the bits for both PowerPivot for SharePoint and for SQL Server Reporting Services. The next step in my case was to run the PowerPivot for SharePoint Configuration tool. There are normally two such tools available, the original one, for SharePoint 2010, and with the release of SQL Server 2012 SP1, there is the “PowerPivot for SharePoint 2013 Configuration Tool” which as the name might imply, is for configuring on SharePoint 2013. However, after this installation, there was only the one tool, which led me to believe that they had been consolidated. However running it resulted in the “Assembly not found” error that you get when installing on the wrong platform.

The next step in installing SSRS is normally to go and add the Service Application in Central Administration. However, it wasn’t available to add, and the typical PowerShell commands to register it (Install-SPRSService and Install-SPRSServiceProxy) resulted in not found errors. Something was clearly amiss.

After poking around a bit I was able to determine that the PowerPivot engine that was running was 11.0.2100.6, which corresponds to the RTM version of SQL Server 2012. (You can find a list of build numbers here) SharePoint Server 2013 requires at least SP1 for PowerPivot for SharePoint and for SSRS integrated mode. The problem had been identified, but what caused it?

After speaking with Todd Klindt (keeper of the SharePoint Patches log), it turns out that he had a similar experience with the SQL SP1 installation media. There is, or at least was a bug in the slipstream version of SQL Server 2012 SP1 that was available through Microsoft’s standard distribution channels. This bug caused only the RTM bits to get installed, not the patches. This has been documented in this Microsoft KB article. The bug was fixed, and the download media was updated within a few weeks. However, it appears that the same production glitch that caused the problem with SP1 happened again with the initial release of SP2, as documented in this blog post.

Happily, the problem has since been corrected, and any new downloads of SQL 2012 with SP2 should not be affected. However, if you have used an affected version, the fix is simple. All that is necessary is that you patch your install with the standalone SQLServer 2012 SP2 installer, available here. Ensure that you patch all elements, including your PowerPivot for SharePoint instance. You’ll know that you have the problem if you don’t see the PowerPivot for SharePoint 2013 configuration tool – there should be two as follows:

Perhaps the SQL team should talk to the SharePoint team about patching. Just sayin’…..

3 Comments

Using the SSIS OData Source Connector With SharePoint Online Authentication

Published March 30, 2014

Last week, Microsoft released the OData Source for Microsoft SQL Server 2012 . What is it? It allows SQL Server Integration Services (SSIS) to use an OData feed as a first class citizen data source in the same manner as SQL Server, Oracle, etc. Until now it was necessary to code OData connections using the script object.

This matters to those of us in the SharePoint world because any SharePoint list data can be expressed as OData.

I’ve written before about how SharePoint data can be extracted into a data warehouse using SSIS and the SharePoint List Source and Destination Adapters, available from CodePlex. These adapters plug in to SSIS and wrapper the SharePoint SOAP web services, and therefore do not need to be installed on a SharePoint server. We have used them for years, and they work very well, however, they are a CodePlex project, and therefore not fully supported.

These CodePlex adapters have more recently been bumping into another limitation. While SOAP web services are supported in Office 365, the adapters don’t support the Office 365 authentication mechanism, which effectively renders them useless. The OData services require the same authentication, but the the new OData Source supports it. It’s also an official Microsoft product, and is fully supported.

There is, however a trick to getting it working. Once you install the OData Source, you open up SQL Server Data Tools, open an SSIS project, and add or edit a data flow task. In the SSIS Toolbox, You should see the OData Source.

Drag the tool on to the design surface, and double click to configure it. You’ll first need to configure an OData Connection Manager, and you’ll do that by clicking the New button.

Give the connection a name. The connection will be common to all lists and libraries within a site, so something based on the name of the site is likely appropriate. The Service document location is the OData endpoint. It takes the form of the URL of the site, along with the suffix /_vti_bin/listdata.svc. If the connection is on premises, you can use Windows Authentication, but if it is Office 365, you must use a stored name and password.

If you are using Office 365, and you click Test Connection at this point, you’ll receive an error “Test connection failed –> The remote server returned an error: (400) Bad Request.”

This is due to Office 365’s “unique” authentication mechanism. In order to authenticate to Office 365, you must first select the “All” button in the toolbar, and set the value of “Microsoft Online Services Authentication” to true.

This option may not be available to you. If the Online Services Authentication option is disabled, or greyed out, as it was for me when I first tried to use it, it’s because a prerequisite is missing. In order to authenticate to Office 365, the machine must have the SharePoint Server 2013 Client Components SDK installed on it.

Once the client components are in place, and the option is selected, the data source should be able to connect to the source, and the connection manager can be closed. Lists are exposed as Collections, so if you want to work with list data, you can then select the list from the list of Collections.

At this point, the data source will act like any other SSIS data source, you can select and transform columns at will. More importantly, this will help you get SharePoint data both on-prem and in the cloud into a central data warehouse.

90 Comments

Business Intelligence in SharePoint and Office 365

Published March 9, 2014

At the recent SharePoint Conference 2014, I had the opportunity to be interviewed by Karuana Gatimu of Microsoft IT and Channel 9. The original video can be found on Channel 9 here, and I include it below as well. In it, we discuss the Microsoft Business Intelligence stack, the impact of the new Power BI products, and how to get started.

Power Query Navigator Can Only View 2,000 Items, and Other Limits

Published July 22, 2013

I was preparing a demonstration using Great Plains data when I came across a curious limitation. Great Plains isn’t exactly efficient with its use of tables, and this database had well over 1,000 tables in it. Unfortunately, Power Query wasn’t showing the tables that I was interested in. What I did notice was the number 1000 beside my table name.

That seemed like a pretty suspiciously round number, so I decided to dig a little and found that with the current version of Power Query, the navigation pane does indeed have a limit of 1000 items per data source. Indeed, there are a number of limitations to be aware of, and Microsoft has published a complete list of Power Query limitations that can be found here.

UPDATE – Nov 2013 – In the Nov 2013 update of Power Query, this update limit has been increased to 2,000. I have updated the title of this post accordingly, but all else here remains valid.

There is a workaround for this limit however. Instead of selecting the table that you are after, select any of the ones that are displayed. The preview data will fill the preview pane. In this case, I need data from the RM00101 table, but I first select the DS10100 table. After the preview is selected, click on the Query Editor button.

Once the button is pressed, you will be presented with an editor screen. Simply replace the name of the table that you don’t want, with the table that you do.

Once complete, clicking on the Done button should fill the preview screen with the desired data, and the navigation pane will show the correct table name.

You can now continue on to select additional tables, or continue your analysis. Feedback from customer support indicated that this limitation will be addressed in an upcoming update.

2 Comments