Issues Moving SharePoint Access Apps Between Farms

I hit an issue recently that was a little tricky in regards to Access Services and SharePoint 2013.  (Not Access Services 2010)  The issue arose when saving an Access app that was created in a development farm and trying to publish it to production.  Here are the steps to create an Access app package for an on-premises Access app.  What was unique about this situation that moving apps in any other direction worked.  You were able to go Prod->Prod, Prod->Dev, Dev->Dev but Dev->Prod failed.  You were also able to create new Access apps in both environments.

When deploying the app to a site in the production environment these were the errors that were received…

————–

DacManagerPeer.Install: Exception thrown installing DAC. Exception {Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. —> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Internal Error.  The internal target platform type Sql110DatabaseSchemaProvider does not support schema file version ‘2.5’.     at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateExtensionManagerFromModel(SqlPackage package)

————–

DacManager.ExecuteDacOperation: DAC operation failed with exception {Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package. —> Microsoft.Data.Tools.Schema.Sql.Deployment.DeploymentFailedException: Internal Error.  The internal target platform type Sql110DatabaseSchemaProvider does not support schema file version ‘2.5’.     at Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeployment.CreateExtensionManagerFromModel(SqlPackage package) 

————–

I had never heard of DAC as it relates to SharePoint (it’s not Dedicated Administrator Connection) before so I had to do some digging.  From the sound of the error it sounded like software was mismatched between environments.  I first double-checked all of the install tasks to ensure they were completed – Here is a great white paper on setting up Access Services.  While going through this I saw that one of the prerequisites referenced DAC, the Data-Tier Application Framework.

image

So I went and looked at both environments and sure enough there were multiple versions of the Data-Tier Application Framework across all servers (APP & SQL) in both environments.  There were versions 10 up to 12 with no standardization.  I then went ahead and installed the latest version of the DACFramework.msi file that I could find in the download center which was from June 22nd 2015 and version 13+.  I received the same error after this install so I started to dig deeper by going into the Access app package.  I first changed the extension of the app package to .zip.

image

And I found these files:

image

Great start, time to compare between apps build in Prod vs Dev!  At first glance nothing stood out between the manifest and .xml files, but then I started looking into the .dacpac file as I had never heard of this before.  Here is what I found:

  • A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects – like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.

It was now making more sense.  Access Services in 2013 uses an entire SQL DB per app you create.  So SharePoint is utilizing the logic behind the DAC framework to package and move databases between environments.  (This took software on the SQL server out of the picture).  But I must go deeper so lets open up this file…. was able to use the same logic as before and convert the extension to .zip to open it up.

image

Yay! More files to open and compare. I first looked at Origin.xml and nearly found what I was looking for.

Dev

image
Prod

image

Dev has a newer DAC version than Prod.  Ok is there anything else?  What is this schema error?  Opening up the model.xml file I found the answer.

image

Inside of the model.xml file existed the corresponding Schema for the DB provider that was throwing the error.  And sure enough apps created in Prod had Schema Version 2.4.  This made sense but I had installed the latest DAC version on both environments?  What I found out is that installing the newest DAC framework version 13.0.2993.2 does not upgrade the previous version.  I then remove everything I can find for older DAC versions and give it a shot.

image

Another error? Come on!  At least I got a correlation ID.

————–

DacManager.ExecuteDacOperation: DAC operation failed with exception {Microsoft.Office.Access.Services.Design.TemplateException: Sorry, an app package could not be created.  

————–

Well that didn’t appear too helpful.  The next shot I took was a guess that the version I installed was too new.  So I installed the Data-Tier Application Framework August 2013 version 11.1.2902.0 and completely uninstalled version 13.0.2993.2.

image

SUCCESS!  I was then able to successfully add an app that was created in the Dev environment to Prod.  Mission completed, let’s load this app and move on…..(queue up loading music)……Error…..dang……

image

————–

We weren’t able to load the app’s home page. Please let your administrator know about this.

————–

Not helpful, onto ULS logs and actually some helpful info.

————–

The application’s system schema version is newer than what the current version of Access Services understands. AppVersion: 15-0-61-0. ServerVersion 15-0-59-0.

An error occurred reading application properties while opening a command scope. Microsoft.Office.Access.Services.VersioningException: Sorry, the server doesn’t recognize the version of the app you are trying to open.

————–

Alright so more discrepancies between farms.  What I found on dev was a hotfix applied KB2956180.  I knew this had been there as this was the first place I looked when we had server mismatches at the beginning.  I didn’t believe that this was vital as the overall version of Access Services was identical between farms and the KB was a lower build number.   Reviewing the KB though had a lot of direct ties into Access Services.  This was also a large hotfix (154MB) and required PSConfig to be ran.

image

I went ahead an installed this hotfix on all SP servers and ran PSconfig on all servers.  I then opened the app that had threw an error earlier and the sky’s opened up with a wonderful ray of light and I saw my Access app.

image

I certainly hope this post will save someone some time as it was quite an adventure.  The final breakdown and advice of this post:

  1. Ensure the same version of DACFramework.msi is installed across your farms
  2. Ensure you only have 1 version across your farms
  3. Do not use version 13.0.2993.2 of DACFramework.msi
  4. Ensure hotfixes (even if lower build numbers) are matched between farms

Happy SharePoint troubleshooting!

SharePoint 2013 PowerPivot Install – Error Deploying Farm Solutions

I ran into an issue recently that I thought would be good to pass along. I was installing the BI features for SharePoint 2013 on a new farm and ran into an issue when configuring PowerPivot. This environment is a 3 tier environment as depicted in the image below.

2015-07-15 13_24_45-Document1 - Word

These are the BI steps that I had already completed. (along with some helpful links)

Here is the download link for SQL Server 2014. The installer does not deploy or configure Power Pivot features in SharePoint. The following components install by default:

  • Power Pivot for SharePoint 2013. This component includes:
    1. PowerShell scripts (.ps1 files)
    2. SharePoint solution packages (.wsp)
    3. Power Pivot for SharePoint 2013 configuration tool to deploy Power Pivot in a SharePoint 2013 farm
  • Microsoft OLE DB Provider for Analysis Services (MSOLAP).
  • ADOMD.NET data provider.
  • SQL Server Analysis Management Objects.

A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including application servers and web-front end servers. The installer package includes the Analysis Services data providers as well as the Power Pivot for SharePoint 2013 configuration tool. When you install spPowerPivot.msi you can customize the installation by excluding individual components.

The next step is to configure the PowerPivot for SharePoint Add-in

This is the area in which I ran into an issue.  Here is the technet article about the configuration.

  1. I launched the configuration toolimage<
  2. I set the default account information, DB Server and PowerPivot infoimage
  3. I set the parameters for the task “Create PowerPivot Service Application” to use my naming format.  (Remove the GUID from the DB)image
  4. I set the web application deployment location for the task “Deploy Web Application Solution”image
  5. I set the unattended account info for the task “Create Unattended Account for Data Refresh”image
  6. I then clicked Validate and Run

The configuration failed on the task “Deploy Farm Solution” with the error:

Running PS Script failed. Reason: Solution failed to deployed, reason: SPAPP01 :
Error: Cannot add the specified assembly to the global assembly cache: Microsoft.AnalysisServices.SPAddin.dll.

The solution DID deploy to SPWEB01 and SPAPP02.

These were the items I tried to debug first all of which failed:

  1. IIS Reset on all servers
  2. Reset Timer Service on all servers
  3. Ran Configuration tool as Administrator
  4. Set default account to different account names with different permissions (farm/install/service/etc…)

I then stumbled across this blog – Troubleshooting PowerPivot for SharePoint Solution Deployments – which lead me into the correct direction based on this statement:

“…the Timer service depends on the Administration service to carry out the deployment work because the Timer service usually runs under a low-privileged service account that might not have the required permissions to deploy assemblies in the Global Assembly Cache and so forth, while the Administration service uses the high-privileged Local System account with full access to all local resources.”

So I head out to SPAPP01 and check out what account is running the Administration service and sure enough it was set to the fully qualified farm domain account!

I reset the log on to be Local System and reran the configuration.

image

Success!

image

Hopefully this helps someone else moving forward!

Microsoft Ignite & SharePoint/O365 – Outcomes

MSIgnite

Well this blog post is coming in quite late as Microsoft Ignite was a little less than a month ago. But I believe in better late than never and there are some good topics that I wanted to follow up with. I was lucky enough to attend Ignite with a great group of folks from Concurrency and was also able to do some great networking to meet new folks in the SharePoint and O365 collaboration world. My initial reaction of Ignite was that it was a little overwhelming at times. Coming from the world of smaller SharePoint conferences having 20k+ people in a giant building with all different types of Microsoft technology led to some long walks and not many deep dive sessions. With the amount of announcements in the Office 365 and SharePoint Server 2016 space that were being discussed it was and still is a challenge to keep up with. Looking at the conference from a strictly SharePoint perspective it felt limited at times. Many of the primary SharePoint sessions were packed to the brim and had to be held in overflow areas. I think this directly spoke to the overwhelming usage that SharePoint has in the enterprise still. I am now very excited to attend the smaller SharePoint specific conferences such as SharePoint Fest and SPTechCon to dig deep into the new experiences. I have been trying to go through all of the videos on Channel 9 but there are so many good ones. If you want to download the videos and slides directly here is a link for instructions on how to do it.

In this post I will try to highlight what I believe to be the best sessions for collaboration around SharePoint and Office 365 and also review my pre-conference predictions.

My prediction outcomes

NextGen Portals

Ok we all knew they were already going to announce something but this still was an exciting topic. The new Knowledge Management portal currently called Codename “InfoPedia” was demonstrated. It was apparent that this portal was still in the early stages of development but their strategy to deploy a KM could be great. The new KM portal will consist of Boards, Articles and Microsites in which users are empowered to generate content quickly in a standardized and already styled way. This leads to a more organically and horizontal growing solution rather than a pre-determined hierarchical solution. Here a great post from Benjamin Niaulin about this topic.

Recommended sessions for this topic:

OneDrive for Business Sync Updates

Again we knew this coming but everything announced here was great news. I could write multiple blog posts on all of the new stuff they announced around this topic but here are the juicy highlights. The new OD4B sync client will use the current OneDrive protocol. There will be a unified sync client across OneDrive and OneDrive for Business platforms and the preview and RTM client will be available by end of year. Some other important things to note with the new client:

    • Selective sync (everyone have a round of applause for this one)
    • No more 20k file limit
    • Support for up to 10GB files
    • Blocking of unmanaged PCs
    • Includes PC and Mac

Recommended sessions for this topic:

Simplified Hybrid with SharePoint

I attended the SharePoint Hybrid pre-conference at Ignite and got to see first hand what is coming with hybrid in SharePoint and Office 365. Overall the strategy is clear to me that hybrid will be the new on-premises. There are features that will only be available in Office 365 and Microsoft’s strategy is not to bring you to the cloud but bring the cloud to you. This will allow enterprises to opt-in to hybrid on your own terms. This was very obvious in their hybrid strategy moving forward. Microsoft is trying to make they hybrid experience transparent. I won’t go deep into any of these strategies but if you want to discuss them just shoot me an email or a tweet. Their primary pillars are:

    • Hybrid Search
    • Hybrid OneDrive
    • Hybrid Extranet
    • Hybrid Team Sites
    • Cloud-drive Hybrid Picker
    • In the future with no further info yet…
      • Hybrid taxonomy story
      • Hybrid DLP
      • Hybrid eDiscovery

Recommended sessions for this topic:

What I hoped to see

Future of Forms

Isn’t this everyone’s favorite SharePoint topic? I came in hoping to hear something about forms, or at least anything. With the incredible amount of announcements there was still nothing new on forms. The current state still exists in which InfoPath 2013 will continue to work in Office 365 and SharePoint Server 2016. The only time I heard forms being discussed in a session was during the MVP panel that I linked to above. The panel confirmed the current state and provided similar input to what I am currently telling my clients. If it is a small list form customization go ahead with InfoPath. If you have a larger and more long term forms requirement it is time to look at a 3rd party or custom development.

Future of SharePoint Workflow

There were not architectural changes announced during Ignite. With a total of 0 sessions and 0 discussions about workflow during Ignite I would tend to lean towards the thought that there will be no architectural changes. Workflow will continue to run on Workflow Foundation 4 as an external resource as it does today on-premises and in Office 365. Now there was some news that will affect workflow creators.

There will not be a SharePoint Designer 2016 but SharePoint Designer 2013 will continue to be supported.

I think this is an important step in the evolution of productivity in SharePoint and Office 365. Obviously SharePoint Designer was built with on-premises as its base. That much control is unnecessary in a cloud solution like Office 365. So on that side it makes sense to start bringing in limits. And of course anyone who has used SharePoint Designer heavily in the past knows it was a very buggy product that loved to crash. It is important to remember that we are over a year away from the release of SharePoint Server 2016 so there will be more news around this topic.

As far as workflow creation, I do believe that this is a step in the right direction and hope to see a browser based workflow creation experience. I will also use this time to plug my session at SPBiz that is directly related to SharePoint Designer workflows. This should be a great free online conference.

Future of Yammer

I was very wrong with my prediction here. I was leaning towards the thought that brand for “Yammer” itself would be going away. It was stated pretty loud and clear that this was not the case. There were multiple sessions around this solution including the Yammer Roadmap. Yammer is here to stay and will have a place in the Office 365 ecosystem. Each experience that comes with Office 365 does have its appropriate use cases. The challenge that we currently are and will continue to face is the confusion around when and where to use an experience. There was even a session around this topic titled How to Decide When to Use SharePoint and Yammer and Office 365 Groups and Outlook and Skype. Obviously if we had to have a major session on this topic there is confusion on what to do. I hope this vision continues to clear moving forward.

One item of note around Yammer and Office 365 is that the UI for Yammer is changing to align better with the rest of Office 365. If you are a part of the Office 365 Network (and if you’re reading my blog and are not, go join it now) you are already seeing these changes happening.

The Site Actions Menu in SharePoint Server 2016 not changing locations from the top right

I can confirm that it is staying in the right from the demos performed. No need for any panic from the community.

Anything else interesting?

I think the winner of most interesting topic during Ignite and so far after Ignite has been Office 365 Groups. Microsoft is putting a ton of time and effort into this collaboration experience. I believe that Office 365 Groups still need some help around the governance and control but they will be a go to solution in the future. Here is a link to a great blog post from Nik Patel that will go into a little more detail. Overall groups will be an experience that encompasses nearly all aspects of Office 365.

yammer-post-image

Recommended sessions for this topic:

Here are some other interesting topics and some sessions about each.

SharePoint Server 2016

Office 365 Security

Office 365 Migration API

 

I look forward to the next Microsoft Ignite conference in 2016 coming back to Chicago on May 9-13. It will be interesting to look back on this post and see how different the landscape moves in just 1 year.

Utilizing the new Related Items column via workflow – Part 2

This is a continuation from Part 1 of the blog post Utilizing the new Related Items column via workflow. In Part 1 I wrote about the scenario that I had in which I wanted to connect a document library and a custom list using both a lookup column and a Related Items column. This post discussed how to make the column visible so it can be added to a list or library. In this post I will discuss a strategy to use a SharePoint 2013 Workflow created by SharePoint designer to interact with the Related items column.

To solve my scenario of showing what documents are connected to the deliverable list, I added the Related Items column to the custom Deliverables list. My hope was that I could perform these steps when documents are uploaded to the deliverable documents library:

  1. A document is uploaded to the deliverable documents library and a selection of the matching lookup from the deliverable list is chosen.
  2. The related items field on the corresponding deliverable list is updated with the document that is uploaded.
  3. As more items are added for the same deliverable list item the related items are appended on top of each other.

I found the format for updating the Related Items column from this blog post by Pieter Veenstra. This is the format for a single record in Related Items:

[{“ItemId”:13,”WebId”:”b95aa3f5-5fc2-4df7-b690-7381971e8ab7″,”ListId”:”7fb76569-48d5-45a6-9224-116ee234c304″}]

This is the format for multiple Related Items:

[{“ItemId”:13,”WebId”:”b95aa3f5-5fc2-4df7-b690-7381971e8ab7″,”ListId”:”7fb76569-48d5-45a6-9224-116ee234c304″},{“ItemId”:14,”WebId”:”b95aa3f5-5fc2-4df7-b690-7381971e8ab7″,”ListId”:”7fb76569-48d5-45a6-9224-116ee234c304″}]

I just needed to find a way to use variables to input this data. I created a list workflow that happened on change and creation that was able to successfully perform these steps using variables! Here is the overall workflow and I will break down the sections below.

2014-09-24-21_44_22-https___my.jci.c[2]

Setting Initial Variables

2014-09-24-22_13_16-https___my.jci.c[1]

This section first pauses for a duration to allow the document to be fully checked in and then sets 2 variables.  The first variable, v_relate_deliverable_intID, gets the ID of the current item lookup value. The second variable, v_temp_related, gets the current status of the Related Items column in the Deliverable list that corresponds with the current item lookup value.

2014-09-24-22_02_04-Lookup-for-Strin[1]

This information is needed to ensure that we don’t overwrite any data that already exists in the Related Items column. This variable is then used in the IF statement. This statement checks to see if there are already Related Items or not.

If we need to add to already existing Related Items

The primary steps of this section build the variables that are needed to take the existing Related Items string value and add on the new document’s string value.

2014-09-24-22_15_42-https___my.jci.c[1]

The first 2 Find statements get the index number at the beginning and the end of the existing Related Item string where the outside brackets exist. For example, a broken down version of an item with 3 Related Items where we want to add a 4th would look like this: [{Item1},{Item2},{Item3}]. The brackets are existing at Index 0 and 24. These values are stored in the variables v_index_1 and v_index_2. Our end goal is to get [{Item4},{Item1},{Item2},{Item3}].

The next 2 Calculate statements add 1 and subtract 1 from the index to be able to perform the substring actions. Continuing my example this sets the 2 new calc and calc2 variables to 1 and 23.

The next statement which starts with Copy from… is actually the action named “Extract Substring of String from Index with Length.” This takes the indexes that we calculated with calc variables and outputs it to the variable output. Continuing my example this variable would now equal {Item1},{Item2},{Item3}.

The next 3 statements are used to build the new Related Item string for the item that we are adding. The format should look like this {“ItemId”:”[%Current Item:ID%]“:WebID”:GUID”,”ListID”:”GUID”}. 3 statements are needed because of a restriction in SharePoint Designer workflows. When trying to put that into a Set workflow variable action you receive the error:

“Using the special characters ‘[%%]’ or ‘[%xxx%]’ in any string, or using the special character ‘{‘ in a string that also contains a workflow lookup, may corrupt the string and cause an unexpected result when the workflow runs.”

2014-10-20 22_07_23-https___my.jci.com_sites_enterpriseIT_unity_pmo

To get around this error, the strings can be built separately and then combined. v_temp_start = {“ItemId”: and v_temp_end = “WebId”:GUID”,”ListID”:”GUID”}. The next command sets the variable output2 to [v_temp_start][Current Item ID][v_temp_end].  This gives us the full Related Item string into a variable. Continuing on the example above we now have {Item4}

The final statement in this section combines the already existing Related Items with the brackets removed with the new Related Item and wraps them in brackets. It sets the variable v_related_items to [[output2],[output]] as seen in the screenshot below.

2014-10-20 22_21_06-String Builder

If there are no existing Related Items

The steps of this section use the same logic as above to get the string value needed for the new Related Items text. We just need to get the string [{Item1}] without manipulating any already existing Related Item text.

2014-10-20 22_24_12-https___my.jci.com_sites_enterpriseIT_unity_pmo

These steps are identical to what is used and are broken into 3 statements to avoid the “Special characters..” error.

Updating the Deliverable List

The last statement before the transition to the end of the workflow is an “Update list item” action that is used to take the newly created Related Items string and update the corresponding item in the Deliverable list.

2014-10-20 22_29_34-https___my.jci.com_sites_enterpriseIT_unity_pmo

We are able to perform this step because we have the Related Deliverable column on our document library which we have put in the variable v_related_deliverable_intID. That means our update action can state in words with a screenshot below:

Update the Related Items column to the variable v_related_items for the item in the Deliverable list in which the Current Item ID matches v_related_deliverable_intID.

2014-10-20 22_32_38-Update List Item

Wrapping it up

Part 1 of this blog series discussed the scenario of connecting a list and a library using both a lookup column and the new Related Items column. This would allow visibility to the the connected content from both the list and the document library. First we needed to make the Related Items column visible and able to add to a list. Part 2 discussed how to update that column using a SharePoint designer workflow.

Below is a final screenshot of a list item with Related Item links that were automatically added to it when a document was uploaded to a corresponding library.

2014-10-20 22_37_12-Deliverable Tracker - Incomplete - Internet Explorer

I also added some JSLink on the web part to display the actual related items instead of just a count. But that is for another blog post! (maybe part 3?)

Utilizing the new Related Items column via workflow – Part 1

This is part 1 of a 2 part serious about using the Related Items column in SharePoint 2013.

I ran into an interesting request recently that ended up with an interesting solution. The request that I had was based around connecting a list and a document library. I built a custom list that was used to track deliverables for a project and all of the information about them. At first all actual document deliverables were “attached” to the list item when it was completed. This was not a good approach as attachments are not crawled and indexed. To fix that first issue, I created a separate document library that would hold the deliverables and added a lookup column back the deliverable list. Here are screenshots of the list and library with the lookup column.

Deliverable List

2014-09-12 21_49_56-Deliverable Tracker - BR 1.1 - Program Benefits Definition - Internet Explorer

Deliverable Documents

2014-09-12 21_47_04-Deliverable Documents - BR1.1 - GPP Deliverable Signoff.pdf - Internet Explorer

This was a good approach to tie the list item and a document together. But then the next question was asked…

If I am on the deliverable list, why can’t I see what documents are connected?

While pondering an answer I recalled that there was a new column in SharePoint 2013 called Related Items. I had used that column in the OOTB (out of the box) way with such things as tasks and videos but I had never tried to manipulate it myself. So I first went to try to add that column to my list to test it out but it was not listed as an available column to add…

Getting the Related Items column

This column is part of the hidden group (_Hidden) by default which does not display in the site columns list. This column can be found via the Task content type and updated to a new group. These are the steps to unhide the column:

  1. Navigate to Site Settings and click Site content types in the Web Designer Galleries section.
    2014-10-05 20_44_11-Site Settings - Internet Explorer
  2. Under the List Content Types section click on Task.
    2014-10-05 20_48_54-Site Content Types - Internet Explorer
  3. Click on the Related Items task link.
    2014-10-05 20_49_40-Content Type - Internet Explorer
  4. Click Edit site column.
    2014-10-05 20_50_25-Change Content Type Column - Internet Explorer
  5. Change the Group settings from _Hidden to an Existing group (such as Custom Columns).
    2014-09-24 21_18_23-Change Column - Internet Explorer

The Related Items column can now be added to a list or library! So I went ahead and added the column to my deliverable custom list and was ready to start figuring out how to use this column effectively.

In the next part I will discuss how to work with the Related Items column via a SharePoint 2013 workflow created in SharePoint Designer.

Link to part 2

Utilizing the new Related Items column via workflow – Part 2