Access Services apologizes – “Sorry, something went wrong”

I was recently working with Access Services in SharePoint 2013 in my dev environment, and every time I created a new App for access I was immediately confronted with the following error “Sorry, something went wrong. An unexpected error has occurred.

clip_image001

Unexpected indeed! Thanks for that very informative error message, your politeness however is of little practical use to me. After spending more time than I care to admit publicly, we were able to eventually track this problem down to the fact that the account running the Access Services service application didn’t have the appropriate permissions to the SharePoint content database. I was using a domain account of CONTOSO\sp_accsvc to run the service application. To fix the problem, we ultimately needed to grant the CONTOSO\sp_accsvc account access to the “SPDataAccess” role in the SharePoint configuration database (in SQL Server). After doing that (and running an iisreset on the SharePoint box) POOF! I’m creating Access Apps error free!

clip_image003

Hopefully this will save someone some time (like myself in the future once I forget).

SSRS Shared vs Embedded Data Sets

In my last post we talked about shared vs embedded data sources, in this post we’ll be talking about shared vs embedded when using data sets.

In SQL Server Reporting Services (SSRS) you create “data sources” to tell your reports where you want to pull your data from, but you then need to create “data sets” to tell the report what data you actually want. Data sets store the query that will be issued against the data source. Just like when you go to create a data source, one of the first decisions you’ll need to make when creating a new data set is if it will be “Shared” or “Embedded”. And just as I mentioned in the last post, choosing wisely and creating the right type from the beginning can definitely save you some aggravation in the long run and make things much easier to manage overall. The difference between a shared and an embedded data sets is pretty straight forward, but let’s go ahead and review them real quick.

clip_image001Embedded data sets: When you create an embedded data set, the query, and which data source the query should be issued against is stored inside of the report itself, and can not be referenced by outside reports.

clip_image002Shared data sets: When you create a shared data set, the query and which data source it references is saved as its own object, independent of any report. When you create shared data sets, they must reference shared data sources.

So that sounds pretty straight forward, but which is best? Again, it sort of depends, but interestingly enough my advice is exactly the opposite of what I gave for Shared Data sources. In general I tend to use embedded data sets almost exclusively. (Opposed to data sources, where I used shared almost exclusively)

My reasoning for using primarily embedded data sources is this: While you’ll probably only have a handful of data sources for a report project, you’ll likely have WAY more data sets. (One for every query your report uses). It’s not uncommon to have 5-8 data sets per report. Lets say you have 20 reports, that turns into a ton of management. Some of the questions you might find yourself asking are:

  • How do you know if a shared data set already exists or if you have to create a new one?
  • If you change one of the data sets, what reports are impacted?

My experience is that shared data sets are seldom reused, and instead data sets are re-created anyway. The one place where I think they can come in handy though is if you’re populating parameter dropdown values with data sets, and several of the reports need to have the same parameter values on/in them. In this case, using shared data sets can be a nice was to drive consistency between reports and save yourself a little work having to re-create the queries for each report.

Have you leveraged shared data sets in your environment? Tell me about its success/failure it in the comments.

SSRS Shared vs Embedded Data Sources

In SQL Server Reporting Services (SSRS) you create “data sources” to tell your reports where you want to pull your data from, and how authentication to the data source should work. One of the first decisions you’ll need to make when creating a new data source is if it will be “Shared” or “Embedded”. Choosing wisely and creating the right type of data source from the beginning can definitely save you some aggravation in the long run and make things much easier to manage overall. The difference between shared and embedded is pretty straight forward, but let’s go ahead and review them real quick.

EmbeddedDSEmbedded data sources: When you create an embedded data source, all of the connection/authentication information for the data source you’re connecting to is stored in the report itself and can’t be referenced by other reports.

SharedDSShared data sources: When you create a shared data source, all of the connection information for the data source you’re connecting to is stored outside of the report, and deployed as its own object. Other reports are able to reference the shared data source.

So that sounds pretty straight forward, but which is best? Well, it sort of depends, but in general I tend to use the following guidelines

1. If I have multiple reports that are going to be referencing the same data source, a shared data source makes perfect sense

2. If I have a data source that I know is only going to be used by one report, and never by any others, then an embedded data source might make more sense, although this is kind of a rare case in my experience. If you do created an embedded data source and start getting requests for other reports using the same data source…you should definitely convert it to a shared data source.

The advantage of using shared data sources starts to become evident once you start to have a large number of reports to manage. If you have to point your reports to different environments like dev / uat / prod, you can change all the reports at once by simply changing the information in the shared data source. Had you created all your reports with embedded data sources however, you’d have to edit each report individually, and if you have a lot of reports, that can translate into a lot of work.

Next post we’ll take a look at shared vs embedded when using data sets.