All posts in PowerShell

Terminate SharePoint 2013 Workflows with PowerShell

Let’s admit it.  Sometimes things just go wrong and you need an easy or straight forward way of cleaning it up and starting over again.  An example of this happened to me the other day where I had a bad workflow that went unnoticed and started suspending.  By the time it was noticed there were about 50+ suspended workflows (it was an active list).  So after stopping the workflow I started working through cleaning it up and thought there has to be a way to terminate SharePoint 2013 workflows with PowerShell.  Turns out I could do it.  Here’s how:

Read more

Proper way to handle workflows that throw a Microsoft.Workflow.Client.ActivityValidationException

I had to build a fairly complex workflow not long ago.  The workflow was built in SharePoint Designer 2013 and had a lot of moving parts to it.  So many, that when I went to publish it I received the following error message: “Microsoft.Workflow.Client.ActivityValidationException: Workflow XAML failed validation due to the following errors: Activity “SomeXActivity” has 65 arguments, which exceeds the maximum number of arguments per activity (50).”  This error message is actually telling us that our workflow has too many variables within it.  Basically, this is happening because when the workflow is running the Workflow Manager has to manage more 58 (in my case) variables.  Workflow Manager only allows there to be 50 variables in the workflow… by default.

Read more

Read XML Data Files Stored in a SharePoint Library with PowerShell

I recently had a need to read data from an xml configuration file that was stored within a SharePoint library.  To make things easier I of course went to Google (or Bing) and checked to see if someone else had yet blogged this.  Couldn’t find anything, so in order to help out someone else that may need to do the same at some point, I wrote up a quick little blog.  Note: this will only work for on-premises versions of SharePoint.  I’ll update with a SharePoint Online version in the future.  I’ll also write up how to add and delete contents in a future post as well.

Read more

Manipulating SharePoint External Lists using CSOM

This is the second part in my series on developing code to modify external lists.  In this post we will be updating SharePoint external lists using CSOM.  Like standard PowerShell there is not any real difference in the execution of the update between internal and external lists.  What is different is how the data is obtained when performing an update, read or deletion.  Like PowerShell, in CSOM you can find the item you wish to access using the ID.  However, because there isn’t a list ID field in an external list you have to query to find the item(s) you need.  You could loop through each item in the list until you find one with the particular attributes you are looking for, but that is not efficient and it may be difficult if the fields you are checking have multiple rows with the same values.  So brush up on your CAML query.

Using CSOM to Manage Data in Internal Lists

CSOM has come a long way since its inception in SharePoint 2010.  I would still be recommending it over REST except that unlike REST things are a bit different between on-prem and SharePoint Online CSOM versions (you need a different versions for each).  This post isn’t meant as a discussion on the pros and cons of REST vs CSOM though (You can get more information here if you are curious though).  CSOM is probably a little more complicated than the PowerShell examples I provided in my previous post, but one you get the hang of loading the data you need before working with it the process is pretty easy.

To show you what we are going to be doing here’s the list we will be starting off with:

Updating SharePoint External Lists using CSOM - Internal List Before Code

Using this config:

we will update the list with the following code:

Running the code:

Updating SharePoint External Lists using CSOM - Internal List Running Code

The list gets updated as expected:

Updating SharePoint External Lists using CSOM - Internal List After Code


Using CSOM to Update External Lists

As I stated above, the code is very similar to internal lists except there you need to use CAML Query to access the items.  Note with CSOM you don’t have to define the ServiceContext scope.

The list:

Updating SharePoint External Lists using CSOM - External List Before Code

Will get modified based on this config:

Reviewing the code, note the insertion of the CAML Query to find a specific item.  One important thing to note with the CAML query.  You have to use the name of the function used to define the Read-List process in the External Content Type

Running the code:

Updating SharePoint External Lists using CSOM - External List Running Code

Leaves us with the following updated list:

Updating SharePoint External Lists using CSOM - External List After Code

So again, other than gathering the item to update, managing items in an external list is very similar to managing items in an internal list.  My next post we will cover the differences in manipulating internal and external items with REST.

Thanks for reading!!

Using PowerShell to Access Data In External Lists

I created my  blog series on SharePoint BCS quite some time ago in order to illustrate how easy it is to access data outside of SharePoint and present it within a list or document library.  I have been meaning for quite some time to demonstrate how writing code for external lists\libraries is very similar to writing code for internal lists\libraries.  Today I am going to start a new three part series on SharePoint BCS development by showing you how you can enhance your solutions using PowerShell to access data in external lists.  The posts aren’t going to be too in-depth, but meant to get you started.  I hope to build on them later and post a series on creating an entire solution around BCS.

Using PowerShell to Manage Data in Internal Lists

With SharePoint PowerShell you are basically using the server object model for development.  So this means you have access to the methods and processes utilised by this system.  What you’ll notice is that code to perform inserts, updates, reads and deletions is pretty much exactly the same.  Where the code differs between internal and external is actually around accessing the item to be updated, read or deleted.

To illustrate I have created a script that performs an insert, update, read and delete.  To show you what’s happening here’s the config for the code:


As you can see the code should insert a new item, update two, read a third and finally delete.  The code is pretty straight forward:

Using the following list:

Using PowerShell to Access Data In External Lists - Internal List Before Code

And running the above script:

Using PowerShell to Access Data In External Lists - InternalListRunningCode

We end up with the result:

Using PowerShell to Access Data In External Lists - Internal List After Code


Using PowerShell to Update External Lists

The code is almost identical.  What actually changes is the code used for finding the items we want to update.  We also have to define the ServiceContext scope to process the transactions.  External list manipulation requires the scope to import the data catalogue (more information can be found here).  First the config to see what the plan is:

Then using the code:

Notice in the code above that we are using a pipe to gather the data.  One thing to note with External Lists is they do not have a numerical ID field.  However, in this case we are grabbing the ID field of the Asset so we can search on the AssetID of the item.

We then update the following list:

Using PowerShell to Access Data In External Lists - External List Before Code

by running the code:

Using PowerShell to Access Data In External Lists - External List Running Code

And receive the following updated list:

Using PowerShell to Access Data In External Lists - External List After Code


That’s the basics of updating an External List with PowerShell.  In the next post I will cover updating using CSOM.


Thanks for Reading!