Using BCS from PowerShell with SharePoint 2013

Running data migrations with SharePoint 2013 often involves using BCS and PowerShell if you don’t have and third party tools at hand. This can be a bit tricky to set up, but once you are familiar the the BCS configuration, the PowerShell scripts are mostly straight forward.

From my experience there are a few differences when running against BCS lists vs. regular lists. I have now been able to find a good guide for this, so from my own trial and error I wanted to share a few tips.

Get the context straight: Accessing the list gives a weird error

Running this:


$Web = Get-SPWeb "http://mybcsweb/"

$Web.GetList("/Lists/MyBcsDataList")

Gives this error:

format-default : The shim execution failed unexpectedly – Proxy creation failed. Default context not found..    + CategoryInfo          : NotSpecified: (:) [format-default], BdcException   + FullyQualifiedErrorId : Microsoft.BusinessData.Infrastructure.BdcException,Microsoft.PowerShell.Commands.FormatDefaultComman

bcs-powershell-sp2013-1

The fix:


$Site = Get-SPSite "http://mybcsweb"

$ServiceContext = Get-SPServiceContext -Site $Site # Required when working with BCS
$ServiceScope = New-Object Microsoft.SharePoint.SPServiceContextScope $ServiceContext # Required when working with BCS

$Web = $Site.RootWeb
$Web.GetList("/Lists/MyBcsDataList")

A bit more wiring is required to get the context straight for BCS to work.

Make sure you are running with administrator privileges

I use PowerShell ISE when creating and running these scripts. From time to time I accidently forget to start this with Administrator privileges. This can fool you into weird errors like this one. Short minded one can try to start adding the missing assembly references, but the real reason is the missing privileges.

Running this:

$Web = Get-SPWeb "http://mybcsweb/"
$Web.GetList("/Lists/MyBcsDataList")

Gives this error:

format-default : The shim execution failed unexpectedly – Could not load file or assembly ‘System.Data.OracleClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542).    + CategoryInfo          : NotSpecified: (:) [format-default], BdcException  + FullyQualifiedErrorId : Microsoft.BusinessData.Infrastructure.BdcException,Microsoft.PowerShell.Commands.FormatDefaultCommand

bcs-powershell-sp2013-2

The fix:

Search for “PowerShell ISE”, right-click it and choose “Run as Administrator”.

bcs-powershell-sp2013-3

Include ViewFields in you CAML queries

Always remember to include the fields you are trying to query on in the “<Where>” statement within “<ViewFields>”. This isn’t required on regular lists, but to get any results in return, I experienced I was required to include them querying BCS lists.

Example of CAML query in PowerShell:


$Query= New-Object Microsoft.SharePoint.SPQuery 

$Query.ViewXml =
 "<View>
 <Method Name='Read List'/>
 <Query>
 <Where>
 <And>
 <Eq>
 <FieldRef Name='ProjectID' />
 <Value Type='Text'>100</Value>
 </Eq>
 <Eq>
 <FieldRef Name='Role' />
 <Value Type='Text'>Project Manager</Value>
 </Eq>
 </And>
 </Where>
 </Query>
 <ViewFields>
 <FieldRef Name='ProjectID'/>
 <FieldRef Name='Role'/>
 </ViewFields>
 </View>" 

 $Items = $List.GetItems($Query)

Summary

BCS has been around for a while and isn’t something very existing. But as mention in the introduction, using it for data migrations is very powerful, specially when you don’t have any 3. parts tools around. Doing this from PowerShell is pretty straight forward, but these three issues I experienced can be a headache if you get stuck with them. Hopefully this post can help others get quickly passed them!

Use PowerShell to create a demosite for all the common used Web Templates in SharePoint 2013

In my previous article I provided a list of all the common used web templates in SharePoint 2013. For demo purposes it can be handy to spin up a copy of all these templates and later easy remove them. Using PowerShell we can accomplish this with the snippets provided below. Change the parameters under “Configuration” to match your own environment.

Create new site collections

This snippet will create a new site collection for each of the web templates.


Add-PSSnapin Microsoft.SharePoint.PowerShell

# Configuration
$webApp = "http://intranet.contoso.com"
$managedPath = "sites"
$siteOwner = "contoso\sp_admin"
$languageId = 1033

# Create site collections for demo
Write "Create demo sites"
Start-SPAssignment -Global
New-SPSite "$webApp/$managedPath/demo-team-site" -Name "Team Site" -Template "STS#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-blog" -Name "Blog" -Template "BLOG#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-dev-site" -Name "Developer Site" -Template "DEV#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-project-iste" -Name "Project Site" -Template "PROJECTSITE#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-community-site" -Name "Community Site" -Template "COMMUNITY#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-document-center" -Name "Document Center" -Template "BDR#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-ediscovery-center" -Name "eDiscovery Center" -Template "EDISC#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-records-center" -Name "Records Center" -Template "OFFILE#1" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-bi-center" -Name "Business Intelligence Center" -Template "BICenterSite#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-ent-search-center" -Name "Enterprise Search Center" -Template "SRCHCEN#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-mysite-host" -Name "My Site Host" -Template "SPSMSITEHOST#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-community-portal" -Name "Community Portal" -Template "COMMUNITYPORTAL#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-basic-search-center" -Name "Basic Search Center" -Template "SRCHCENTERLITE#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-visio-process-rep" -Name "Visio Process Repository" -Template "visprus#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-pub-portal" -Name "Publishing Portal" -Template "BLANKINTERNETCONTAINER#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-ent-wiki" -Name "Enterprise Wiki" -Template "ENTERWIKI#0" -OwnerAlias $siteOwner -Language $languageId
New-SPSite "$webApp/$managedPath/demo-prod-catalog" -Name "Product Catalog" -Template "PRODUCTCATALOG#0" -OwnerAlias $siteOwner -Language $languageId
Stop-SPAssignment -Global

Clean up and remove all the sites

This snippet will delete all the site collections created with the snippet above.


# Configuration
$webApp = "http://intranet.contoso.com"
$managedPath = "sites"
$siteOwner = "contoso\sp_admin"
$languageId = 1033

# Remove site collections
Remove-SPSite "$webApp/$managedPath/demo-team-site" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-blog" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-dev-site" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-project-site" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-community-site" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-document-center" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-ediscovery-center" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-records-center" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-bi-center" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-ent-search-center" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-mysite-host" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-community-portal" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-basic-search-center" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-visio-process-rep" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-pub-portal" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-ent-wiki" -Confirm:$false
Remove-SPSite "$webApp/$managedPath/demo-prod-catalog" -Confirm:$false

Summary

This article demonstrated how easy we can create a copy of all the common used web templates in SharePoint for demo purposes, and later clean it up.

Remove orphaned tasks from the aggregated task list on MySite in SharePoint 2013

For some reason a deleted task was still visible in the aggregated task list for a end-user. The task itself had been deleted from the source site, so when you clicked the task you got an error message telling you it did not exist anymore. The problem was that SharePoint was unable to remove the task from the aggregated view, so now we had to deal with a ghost task!

Solution

The new task aggregation is performed with the help of the Service Application “Work Management Service”, and the users MySite. When the service has aggregated your tasks, it stores the data itself in a list called “WmaAggregatedList_User”. Since it is a traditional list,  you might think: “This is easy! Just go to the list and delete the task!”. Sorry, but no. This list is only intended as a system list, and nothing we ever should care about, so it’s actually has no available views.

Step-by-step to remove the task

  1. Start “SharePoint Manager 2013” on one server in the farm (download from CodePlex)
  2. Navigate to the correct web application, and locate the users MySite site collection under “/personal” or your preferred managed path.
  3. Expand the structure and locate the list “WmaAggregatorList_User”, and choose to browse the “GridView”. If the user doesn’t have to many tasks, you should now be able to use this to visualy inspect the data.
  4. Locate the column named “TxEditUrl” and verify that it matched the URL of the ghost task. In my case I ignored the “&source=” end of the URL. Make a note of the list item ID for the task (the first column)
  5. Fire up good old “SharePoint 2013 Management Shell” to do some PowerShell magic.
  6. Example how to locate the task and remove it:

# Open the users personal site colletion and retrieve the list
$web = Get-SPWeb "http://mysite/personal/adamb"
$list = $web.Lists["WmaAggregatorList_User"] 

# Get the task with the ID located with SharePoint Manager
$item = $list.GetItemById(1) # NOTE: Use the correct ID here

# Now delete the task
$list.Items.DeleteItemById(1)

Summary

In some very rare cases, you can end up with users having orphaned tasks in their aggregated task list on MySite. I never got a understanding why this happened, but it was possible to remove this and get thing back to normal.

Using the SharePoint 2013 social Newsfeed on other sites than the MySite host

The “Newsfeed” found on MySite, is a web part with the same name, and can easily be added to other sites if you enable a hidden feature. From what I can tell, this work fine, but not perfect.

The reason I don’t feel this is a perfect good solution is due to a bug within the web part. The bug is a bit hidden if you haven’t tested all the features within the the feed. This bug has not been fixed at the time of writing, and also present in Office 365.

Enable the “Newsfeed” web part

To enable the social newsfeed on another site, first you have to activate a hidden feature named “MySiteLayouts” in PowerShell (replace the URL with your site):

Enable-SPFeature -Identity "6928b0e5-5707-46a1-ae16-d6e52522d52b" -Url http://sp2013

Now the WebPart we are looking for, Newsfeed, is available at the current site under the “Social Collaboration” group:

mysite-newsfeed-1

The “Newsfeed” now appears as expected, and we can start using it without any more configuration. If you place both the Newsfeed and Sitefeed on the same page, then the Newsfeed won’t load. Not that I can find any good reason why you even would think of doing so… well, I tried… 😉

The bug I mentioned above is hidden in a popup-notification when you create a new task to follow up a social post.

Reproducing the bug in the newsfeed

Post a new message to the feed, and open the context menu on the message by selecting “…”. Choose “Follow up”.

mysite-newsfeed-2

A popup in the upper right corner will appear like this example:

mysite-newsfeed-3

If you now try to click the link in the popup like it states, then you get in trouble:

mysite-newsfeed-4

You will end up with this spinner. From the URL, I assume that this feature was designed for a relative linking to the MySite host. The same bug can also be found in the “Sitefeed” web part.

Summary

It is possible to use the standard “Newsfeed” from MySite on other sites in SharePoint. Even if the feed appears to work pretty fine, there is a bug hidden below the “Follow up” function. This combined with the feature being hidden, using the feed in other ways than Microsoft intended us to do might not be a risk free alternative.

So instead of trying to re-engineer a social intranet site somewhere else, why not build the intranet on the MySite Host? I have also explored this approach, and might share more details here in the future.

Export-SPWeb fails with “These columns don’t currently have unique values”

During a content database migration from SharePoint 2010 to 2013, we also had a requirement to move a few sites (SPWeb) to new locations. The plan was to first mount the Content Database on the SharePoint 2013 farm, create an evaluation upgrade site collection and then export the content from the upgraded site using “Export-SPWeb”. This operation failed, and after several retries it seems like this is not supported.

PowerShell command to export site:

Export-SPWeb http://sps:8080/ -Path "C:\Backup\sps-8080.bak"

This error was found in the log file:

[13.01.2014 10:26:58] FatalError: These columns don't currently have unique values.
[13.01.2014 10:26:58] Debug:    at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint)
   at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign)
   at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign)
   at System.Data.DataRelationCollection.DataSetRelationCollection.AddCore(DataRelation relation)
   at System.Data.DataRelationCollection.Add(DataRelation relation)
   at System.Data.DataRelationCollection.Add(String name, DataColumn parentColumn, DataColumn childColumn)
   at Microsoft.SharePoint.Deployment.ListObjectHelper.GetNextBatch()
   at Microsoft.SharePoint.Deployment.ObjectHelper.RetrieveDataFromDatabase(ExportObject exportObject)
   at Microsoft.SharePoint.Deployment.ListObjectHelper.RetrieveData(ExportObject exportObject)
   at Microsoft.SharePoint.Deployment.ExportObjectManager.GetObjectData(ExportObject exportObject)
   at Microsoft.SharePoint.Deployment.ExportObjectManager.MoveNext()
   at Microsoft.SharePoint.Deployment.ExportObjectManager.ExportObjectEnumerator.MoveNext()
   at Microsoft.SharePoint.Deployment.SPExport.SerializeObjects()
   at Microsoft.SharePoint.Deployment.SPExport.Run()
[13.01.2014 10:26:58] Progress: Export did not complete.

Solution

This problem seems to occur once you have created an evaluation site within the same content database. Deleting the evaluation site does not fix the problem unfortunately.

  • Restore site collection from backup to a new content database
  • Export data with Export-SPWeb from the site, but avoid using an evaluation site.

It is possible to run Export-SPWeb both when the site collection is in 2010-mode and naturally after upgrading it to 2013 as long you stay away from creating an evaluation site.

Summary

If you plan to reorganize the content in the same operation as a content database migration from SharePoint 2010 to 2013, avoid using an evaluation site as it leaves your entire site collection in a state where content cannot be exported. With this experience I now always take a extra site collection backup before using creating evaluation sites (which itself is a excellent feature).

Preparing the users MySite after a upgrade from SharePoint 2010 to 2013

A common part of a migration from SharePoint 2010 to 2013, is to include the MySite user profile and personal site collections. Both the User Profile and Managed Metadata service applications must be migrated first. After the web application has been created, and the content database(s) has been reattached, the MySite host must be upgraded to 2013. This can easily be done either from the web page or with PowerShell. All customization to the branding will be lost as the Master Page is reverted to “mysite15.master”, and you will be required to create a new Master Page based on this one to keep a custom branding. In 2013 the user will now be presented with a dialog with 1-2 options the first time they visit their MySite after the upgrade. To avoid unnecessary interruption and confusion, I think it is a good think to prepare the MySite so these choices is already set for the user from a company policy.

What will it look to the end users?

Scenario 1: The user only has a user profile, and no personal site collection

mysite 2

This gives the user the option to have some of the options regarding the social features in their user profile activated. It could be better to set this for all users, and give them a guide how to changes it afterwards instead. Most people won’t care, and keep the default settings.

Scenario 2: The user had both  a user profile and a personal site collection

mysite 1

In the last option, SharePoint has detected that the user has a site collection, and that one or more document libraries exists within it. Keeping this option without being aware of what it does can end in trouble.

For a personal site collection with no customization, this probably will work out fine. But if you have created personal document libraries, or even having custom solutions with their own document libraries, this must be handled differently.

What happens if I choose “Ok” (as ALWAYS)?

If the document library “Shared Documents” exists, it is automatically mapped to the folder “Shared with everyone”. All other document libraries are created as new folders. A few libraries are always ignored; Style Library, SiteAssets and FormServerTemplates.

For testing purposes it is possible to run the initial setup multiple times as long you clean up the “Documents” library to only contain the “Shared with everyone” folder.

After the files have been moved to SkyDrive, the original library is removed. SkyDrive itself is stored in the “Documents” library with the hard coded URL “/Documents”.

Permissions are not copied, so if you had libraries, folder or files with unique permissions set, these must be reapplies manually after the merge.

It can be a bit tricky to test and get a good understanding of what will happen, but luckily this one-time dialog can be open up and reapplied as many times you want by using this URL: http://mysite/_layouts/15/InitialSetup.aspx?IsDlg=1&HasMysite=1

To simulate the last option in the dialog box, switch the query attribute “HasMysite” between 0 and 1.

Disabling the default “Let’s get social” dialog

Found a hint at this blog, but it didn’t for some reason work for me: http://www.ilovesharepoint.com/2013/03/get-rid-of-mysite-lets-get-social-dialog.html.

By adding the value to the AllProperties, instead of Properties bag of the SPWeb object as suggested in the article above, the dialog was suppressed.

$web = Get-SPWeb http://mysite/
$web.AllProperties["urn:schemas-microsoft-com:sharepoint:portal:profile:SPS-O15FirstRunExperience"] = "Off";
$web.Update();

Just as a reminder when you are removing this dialog, you should make sure the default MySite configuration that is correct for your organization.

Enable social data for existing users

For all new users, these settings are configured and managed by the service application. For existing users, a policy to enable social data can be applied with PowerShell.

mysite 3

$site = Get-SPSite -Limit 1
$context = Get-SPServiceContext $site
$profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
$profiles = $profileManager.GetEnumerator()
$profiles | where {  $_.ShareAllSocialData($true); }

Summary

After migrating MySite from SharePoint 2010 to 2013, new features including Social and SkyDrive requires end-users to take action. This article discusses what these options include, and how you could set the policy up front, and suppress the dialog from appearing at all.