Nov 19, 2011

Issue with Azure SDK on a SharePoint Server

Few weeks ago I and Hilton Giesenow prepared togheter a SharePoint & Azure session for the TechEd Africa and the European SharePoint conference. During the preparation we discovered an issue running the Azure Compute Emulator on a SharePoint box (Azure SDK 1.5 and 1.6). First it seemed that the user profile service screwed up after you had started the emulator for the first time, but further investigations had shown that many service applications didn’t work properly anymore. The problem was, that the emulator sets the IIS anonymous user account to the application pool’s identity. Many thanks to Wictor Wilen for telling us which IIS setting has been affected ( we owe you a pint=>summit 2011 :)

The Error

The SharePoint error after you have started the Azure Compute Emulator for the first time:  “Object reference not set to an instance of an object”
image
SharePoint trace:
Unexpected System.NullReferenceException: Object reference not set to an instance of an object.    
at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.get_ApplicationProperties()     
at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.get_PartitionIDs()     
at Microsoft.Office.Server.Administration.UserProfileApplicationProxy.IsAvailable(SPServiceContext serviceContext)     
at Microsoft.Office.Server.WebControls.MyLinksRibbon.get_PortalAvailable()     at Microsoft.Office.Server.WebControls.MyLinksRibbon.EnsureMySiteUrls()     
at Microsoft.Office.Server.WebControls.MyLinksRibbon.get_PortalMySiteUrlAvailable()  

Fix it

To fix this you have just to set the IIS anonymous user back to IUSR. But unfortunately you have to do this every time after you have run the emulator. 

Open IIS Manager (inetmgr) and do the following steps.

 image
image

image
Done.

Fix it with PowerShell

If you don’t like clicking like a monkey, you can fix it with PowerShell Smile
PS> import-module webadministration

PS> set-webconfigurationproperty /system.webServer/security/authentication/anonymousAuthentication -name userName -value "IUSR"


Hope this will save you time.

Nov 9, 2011

Programming with External Lists In SharePoint Online (Office 365)

In my previous post I announced that SharePoint Online (SPO) in Office 365 now supports Business Connectivity Services (BCS) and shown an example how to connect SPO via BCS to SQL Azure. In this post we’re going to have a look at what you can do programmatically with external lists in SPO. In general we’ve three options: Sanboxed Solutions, Client Object Model and SharePoint Web Services.

Sandboxed Solutions

Especially using the sanboxed object model to program against  external lists in SPO  sounds promising. Just imagine, event receivers and workflow actions that can access external systems through an external list. I was really excited! But the disillusionment followed soon. It seems you can’t connect external systems in SPO via BCS from within sandboxed solutions!
If I try to programmatically access an external list within a sandboxed web part, I get an “The shim execution failed unexpectedly - Access is denied….” exception.
SPList externalList = SPContext.Current.Web.Lists["Customers"];

foreach (SPListItem externalItem in externalList.Items)
{
  writer.Write(externalList.Item["CustomerID"] + ", ");
  writer.Write(externalItem["CompanyName"] + "<br/>");
}

SNAGHTML8ab405
The sandbox stripes out the user’s security token and this means that the credential mapping (e.g. All Users) defined in the Secure Store Service doesn’t work within in the sandbox. The behavior is described in following MSDN article. The suggested work around is that the managed account that runs the user code proxy service (SPUCWorkerProcessProxy.exe) is mapped to the external credentials. But this is not possible in SPO Sad smile

Client Object Model

What indeed does work is using external lists with Client Object Model. There is nice post about this from Steve Fox.
You can use the SharePoint ECMA Client Object Model to access the external customer list from my previous post.
<script type='text/javascript'>
ExecuteOrDelayUntilScriptLoaded(function () {

  var ctx = new SP.ClientContext.get_current();
  var web = ctx.get_web();
  var list = web.get_lists().getByTitle('Customers');
  var query = new SP.CamlQuery();

  query.set_viewXml('<View><ViewFields><FieldRef´Name="CustomerID" /><FieldRef Name="CompanyName" /></ViewFields></View>');
var items = list.getItems(query);

//If you try to use a "default" ClientContext.Load of ListItem data from an external list, 
//you will get the following error: "The given key was not present in the dictionary." Instead, you need to explicitly specify the fields you want in the CamlQuery, 
//and also in the ClientContext.Load method.
  ctx.load(items, 'Include(CustomerID, CompanyName)');

  ctx.executeQueryAsync(
    function (sender, args) {
      var strHtml = "";
      var listItemEnumerator = items.getEnumerator();
      while (listItemEnumerator.moveNext()) {
         var item= listItemEnumerator.get_current();
         strHtml += "<p>" +  item.get_item('CustomerID');
         strHtml += "," + item.get_item('CompanyName') + "</p>";
      }
      $get('output').innerHTML = strHtml;

    }, function (sender, args) { alert(args.get_message()); });

 }, "sp.js");

</script>
<div id='output'></div>

SNAGHTML886a49

Web Services

Another way to program against the external list in SPO is using the SharePoint built-in Lists.asmx web service.

Summary

Although it's nice that SharePoint Online in Office 365 now supports BCS, but as developer I really miss the support for the sandboxed object model. I hope Microsoft will add it with the next service update.

Nov 5, 2011

Connecting SQL Azure To SharePoint Online with BCS

*Diret connections to SQL Azure aren't supported by SharePoint Online. You need a Azure WCF Service as a wrapper around SQL Azure. Steve Fox has written a blog post about this: Leveraging Windows Azure WCF Services to Connect BCS with SharePoint Online. Seems that it had worked was a bug ;-)*

Exciting news SharePoint Online now supports Business Connectivity Services (BCS)! What many people really missed in SharePoint online was the ability to connect to external systems. With the last update Microsoft has enabled the eagerly awaited BCS support in SharePoint Online. BCS within SharePoint Online supports SQL and WCF connections which you can easily setup with SharePoint Designer. What is not supported are custom .NET connectors build in Visual Studio. The reason for this restriction is the fact that custom .NET assemblies have to be deployed to the global assembly cache on the server and this sort of deployments aren’t allowed in SharePoint Online.
In this post I’m going to show you how to connect SharePoint Online to SQL Azure.

Setup Northwind Sample DB in SQL Azure

If you haven’t an Azure account yet, you can request a free trial here. Go to the Azure Management Portal and select database on the left pane. Choose your subscription and create a new database server. Enter your admin credentials for the server and add a firewall rule to enable connections from outside (IP Range 0.0.0.0 – 255.255.255.255). Create a new database called Northwind. In the ribbon click on manage to open the SQL Azure Management tool. Login, go to database schema and data and click open query and upload and run the following SQL script (the script has been adapted from the following CodePlex project http://nwindazure.codeplex.com).

Configure SharePoint Secure Store

In order to be able to connect BCS to SQL Azure we must add the SQL credentials to the Secure Store in SharePoint Online. Go to the SharePoint Online Admin Console within the Office 365 Management Portal. You will find two new menu items (Manage Business Data Connectivity and Manage Secure Store Service).
image
Go to “Manage Secure Store Service” and create a new Secure Store Application called “Northwind” as shown bellow.
image
image
After the application has been created you need to set the credentials. You could create a new user in SQL Azure or you can use the admin user that you have specified when you’ve created the database (not recommended for production).
image
image

Configure Business Data Connectivity Services

Before you can create a new external content type you have to give your user admin rights in BCS. Go to “Manage Business Data Connectivity” and  select “Set Metadata Store Permissions”.
image
image

Create the External Content Type in SharePoint Designer

Now we can create a new external content type in SharePoint Designer. Start SharePoint Designer and open a site in SharePoint Online.
image
Name the external content type “Customer” and choose the external system to connect to.
image
Choose “SQL Server” for data source type.
image
Copy and paste the full qualified name of your Azure SQL Server to the “Database Server” field (you can find the name in the Azure Management Portal). Choose “Connect with Impersonated Custom Identity” and enter “Northwind” (the name that we have defined for our Secure Store Application).
SNAGHTML4499d00
Create all operations for the Customer table (read list, read, edit, new, delete)
image
Go to the following dialogs with next, next, next and save the external content type. To create a list based on on our new external content type select “Create Lists & Form” (optionally you can decide to generate infopath forms instead of usual SharePoint list forms ).
image

Set Permissions for The External Content Type

To define who has access to our new external content type go back to the SharePoint Online Admin Console and navigate to “Manage Business Data Connectivity” and choose set permissions.
image
image

Test It

Now check that everything works as expected. Open your team site and navigate to your new external list.
image

The End

In my next post I’ll show you how to connect an Azure WCF Service to SharePoint Online.

Jul 5, 2011

The Perfect Loop - Looping Through All Webs in A Site Collection

Adequate memory management and disposing are still hot topics in SharePoint 2010. That’s why I would like to share some insights about iterating through a SPWebCollection.
Take a look at the following snippets:
using(SPSite site = new SPSite(“http://myserver/mysitecol”)
{
          foreach(SPWeb web in site.AllWebs)
          {
                    Console.WriteLine(web.ServerRelativeUrl + “: ” + web.Title);
                    web.Dispose();
          }
}
-or in SP context -
foreach(SPWeb web in SPContext.Current.Site.AllWebs)

      writer.Write(web.ServerRelativeUrl + “: ” + web.Title +”,”);
      web.Dispose(); 
}

what happens in the background

The call to the AllWebs property will instantiate a new instance of SPWebCollection (if there is not all ready an instance).
When the iterator moves to the first item in the SPWebCollection and the collection has not already been initialized it will executes a single unmanaged API call to fetch the most important meta data for all the webs within the site collection. You could say it loads the “Header” data of the SPWeb objects. The unmanaged COM API is wrapped within an internal class called SPRequest. You could say SPRequest is kind of a data access layer for SharePoint. SPRequest will then call the stored procedure “proc_ListAllWebsOfSite” to load the required data from the content database.
After the above operation has finished you can access the following properties of each web without any further database round-trip: 
    • ID
    • Title
    • Name
    • ServerRelativeUrl
    • Description
    • Language
    • Created
    • Modified
    • Template
    • Configuration
    • UserIsWebAdmin
    • UIVersion
    • MasterUrl
    • CustomMasterUrl
    • MeetingCount (in case of an SPMeeting web)
As long as you only access this properties the loop will be fast.
Output from the developer dashboard when I run the above snippet in a web part. My test site collection contains 1000 websites.
image 

BUT What happens when I access a property that has not been loaded YET?

When you access a property of the SPWeb that has not already been loaded (maybe by accident or through lack of knowledge) by “proc_ListAllWebsOfSite”, SharePoint  will reload them (lazy loading). You could say the “body” of the SPWeb gets loaded.
using(SPSite site = new SPSite(“http://myserver/mysitecol”)
{
   foreach(SPWeb web in site.AllWebs)
   {
      Console.WriteLine(web.AlternateCssUrl + “: ” + web.Title);
      web.Dispose(); 
   }
}
Output from the developer dashboard when I run the above snippet in a web part.
SNAGHTML17ae3ff 
The first thing that we notice is that SharePoint now does a database request for every SPWeb in the loop! Very expensive regards resource usage and performance!
SNAGHTML17edca0 
Secondly we notice that that there are now many SPRequest allocations. Internally each SPWeb gets it’s own instance of SPRequest assigned when its “body” will be loaded. The call of web.Dispose() helps to get rid of them as fast as possible.

New in SharePoint 2010 – SPWebInfo

As you could see it is really bad for the performance when you access an unloaded (body) property of SPWeb.  That’s one of the reasons why the SharePoint team adds a new property called WebsInfo to SPWebCollection. WebsInfo is a collection of SPWebInfo and each SPWebInfo object is a wrapper for the SPWeb header that will be loaded in the background when you access the AllWebs collection of the SPSite. Internally SPWebInfo will not allocate any SPRequest and must not be disposed.
The perfect loop:
using(SPSite site = new SPSite(“http://myserver/mysitecol”)
{
   foreach(SPWebInfo webInfo in site.AllWebs.WebsInfo)
   {
        Console.WriteLine(webInfo.ServerRelativeUrl + “: ” + web.Title); 
   }
}

-or in LINQ
using(SPSite site = new SPSite(“http://myserver/mysitecol”)
{
     site.AllWebs.WebsInfo.ForEach(wi=>Console.WriteLine(wi.ServerRelativeUrl));

}


image

Jun 1, 2011

Lookup Field With Picker 2010

I’m glad to announce the new release of Lookup Field with Picker. Lookup Field with Picker allows you to select list items from a lookup list via a searchable and configurable picker dialog.

Features

  • NEW Cross site picking
  • Single- and multi-Selection Mode
  • Search in picker dialog
  • Search Operators: equals, not equal, contains, begins with, greater than, greater or equal than, less than, less or equal than.
  • Select fields you would like to search for.
  • Relationship behaviors
  • Supports default values (constants, current user id and default values by url param, useful for 1-n scenarios) must be configured via PowerShell
  • Languages: English, German
  • Delivered as SharePoint Solution Package (WSP)

Not supported

  • Document Information Panel
  • Data Sheet Views

Requirements

  • SharePoint Foundation 2010

Installation

Install Lookup Field with Picker with the following commands within the SharePoint Management Shell:

  • Add-SPSolution "C:\Install\iLoveSharePoint.Fields.LookupFieldWithPicker.wsp"
  • Install-SPSolution ilovesharepoint.fields.lookupfieldwithpicker.wsp -WebApplication http://myserver –GACDeployment

Usage

Add Lookup Field with Picker

You can add a Lookup Field with Picker column either to a list or a content type.

image

Configure Lookup Field with Picker

Select the source site, list and column of the lookup.

image

Select the columns that you want to use for search in picker dialog.

image

Select the additional columns that you want to show in the target list.

image

Choose whether multiple values are allowed or not and what should be happen when the picked item is being deleted. The relationship behaviors “restrict delete” and “cascade delete” are only supported for the single lookup mode (Known Issue: When you choose multiple values and select any other relationship behavior than “none” an exception will be thrown).

image

Pick Items

Open the picker dialog (or enter a value and resolve the entity with the first button)

image

Find a and select item(s) within the picker dialog.

image

Done.

image

Apr 15, 2011

Interview With Marc Anderson, Founder OF the SPServiceS Library on COdeplex

To follow up on my previous post “SharePoint is the #1 Search Term On Codeplex”, I’ve published a short interview with SharePoint MVP Marc Anderson, founder of the jQuery Library for SharePoint Web Services (SPServices) library on CodePlex. Currently SPServices is the top SharePoint search result on CodePlex (sorted by relevance). Interested in what Marc has to say:

Apr 10, 2011

SharePoint is the #1 Search Term On Codeplex!

Short interview with Matt Hawley one of the developers of Microsoft’s open source platform CodePlex. What’s the most searched term on CodePlex?

The current top ten SharePoint projects on CodePlex (10.04.2011)

1. jQuery Library for SharePoint Web Services

This is a jQuery library which abstracts SharePoint's Web Services and makes them easier to use. It also includes functions which use the various Web Service operations to provide more useful (and cool) capabilities. It works entirely client side and requires no server install.

Page Views: 12544* | Downloads: 821* | Started: Aug 19 2009 | Contributors: 2 | License: MIT
Tags: Sharepoint, jQuery, Ajax, Cascading dropdown, client-side, SharePoint 2007, Sharepoint Designer

2. Community Kit for SharePoint

The Community Kit for SharePoint is a set of best practices, templates, Web Parts, tools, and source code that enables practically anyone to create a community website based on SharePoint technology for practically any group of people with a common interest.

Page Views: 6716* | Downloads: 1009* | Started: Feb 20 2007 | Contributors: 76 | License: Ms-PL

Tags: Sharepoint, CKS, Community, WSS, community kit, Community Kit for Sh, SharePoint 2007

3. SharePoint Manager 2010

The SharePoint Manager 2010 is a SharePoint object model explorer. It enables you to browse every site on the local farm and view every property. It also enables you to change the properties.

Page Views: 6550* | Downloads: 1394* | Started: Dec 12 2007 | Contributors: 4 | License: GPLv2

Tags: Sharepoint, SharePoint 2010, administration, QTG, QTG-SharePoint, SharePoint 2007, SharePoint Adm Tools

4. WSPBuilder (SharePoint WSP tool)

A SharePoint Solution Package (WSP) creation tool for WSS 3.0 & MOSS 2007 No more manually creating the manifest.xml file. No more manually specifying the DDF file. No more using the makecab.exe application.

Page Views: 6237* | Downloads: 955* | Started: Jun 4 2007 | Contributors: 4 | License: GPLv2

Tags: Sharepoint, WSPBuilder, Sharepoint Tools, MOSS, MOSS webparts, myfav, QTG

5. Useful Sharepoint Designer Custom Workflow Activities

This project takes aim at making it easier to create advanced workflows using Sharepoint Designer by providing a set of custom workflow activities.

Page Views: 5601* | Downloads: 322* | Started: Jul 4 2007 | Contributors: 8 | License: Ms-PL

Tags: Sharepoint, workflow, MOSS, Sharepoint Designer, WSS, SharePoint Workflow, SPD

6. SharePoint Learning Kit

The SharePoint Learning Kit is a community-source eLearning tool that integrates with Learning Gateway. Rich content is supported with full SCORM 2004 compliance and basic functions are supported for any electronic document.

Page Views: 4297* | Downloads: 326* | Started: Jun 30 2006 | Contributors: 5 | License: Ms-PL

Tags: Sharepoint, SLK, SharePoint Learning , Education, Learning, scorm, eLearning

7. Starter Master Pages for SharePoint 2010

Starter Master Pages for SharePoint 2010 are a clean, commented starting point for creating your own SharePoint 2010 branding. Starter Master Pages were known previously as Minimal Master Pages in SharePoint 2007.

Page Views: 3025* | Downloads: 617* | Started: Nov 18 2009 | Contributors: 1 | License: Ms-RL

Tags: SharePoint 2010, masterpage, Sharepoint, 2010, Branding, Master Page, minimal master page

8. SharePoint Management PowerShell scripts

This project site stores all the PowerShell scripts developed for SharePoint farms (WSS or MOSS) management. Each script is available independently and can be modified as desired

Page Views: 2170* | Downloads: 581* | Started: Jan 11 2009 | Contributors: 9 | License: Ms-PL

Tags: Sharepoint, powershell, management, MOSS, PowerShell Scripts, scripts, Sharepoint Admin

9. patterns & practices SharePoint Guidance

We develop guidance to help devs and architects build applications on SharePoint.

Page Views: 2688* | Downloads: 456* | Started: Jul 30 2008 | Contributors: 8 | License: Custom

Tags: Sharepoint, patterns & practices, SharePoint 2007, Guidance, MOSS, SharePoint Architect, SharePoint Dev Tools

10. iLove SharePoint

SharePoint Tools: Lookup Field with Picker, PowerShell Scripts, SharePoint Designer Actions, PowerActivity, PowerWebPart, PowerEventReceiver... ...you will love it too.

Page Views: 2526* | Downloads: 251* | Started: Jul 2 2008 | Contributors: 2 | License: GPLv2

Tags: Sharepoint, powershell, .NET, lookup, MOSS, Sharepoint Designer, wcf

At least I’m in the top ten Smile

Apr 3, 2011

MVP Award PART II

I’m glad to announce that I have been awarded to a SharePoint MVP for the second time.

MVP_Horizontal_FullColor

Thanks to Microsoft for the recognition of my effort and to the amazing SharePoint Community around the world for their support.

See you…

Why Andrew Connell loves sharepoint

Short interview with Andrew Connell (SharePoint MVP) about SharePoint 2010 on the MVP Summit 2011 Product Group Evening at the Lucky Strike bar.

Interview with Andrew Connell about SharePoint 2010

Mar 20, 2011

Sharecamp 2011 - born2share.

I’m happy to announce the ShareCamp 2011. It will be at the weekend from 14th to 15th May at the Microsoft Headquarter in Munich. The ShareCamp is a BarCamp about SharePoint. Our slogan is born2share. (spoken born to SharePoint). We did it for for the first time last year in April and were very curious if it would be a success or not. And it was a great success! We got really amazing feedback and actually I would say it has become THE SharePoint community event in Germany!

ShareCamp 2011

The idea for this kind of event arise from the insight that the commercial events often tend to be one-sided, uncreative and boring. Always the same sessions, same speakers and novice session levels. So the ShareCamp was born! As I mentioned earlier the ShareCamp is a special kind of an unconference, a such called BarCamp. The base idea is that there are no predefined time schedules, sessions and speakers. Everyone should contribute! It doesn’t matter if you are an end user, admin, manager, developer, novice, expert, speaker, geek or MVP if you have something to say, say it!

Sounds crazy, doesn’t it? But it works! Be part of it and register now! (The attendance is for free but the seats are limited!)

12324_112736402080637_106328299388114_195378_3341434_n[1]12324_112650775422533_106328299388114_194872_8035884_n[1]12324_113178885369722_106328299388114_197446_8382896_n[1]12324_112736405413970_106328299388114_195379_1647201_n[1]

12324_112736508747293_106328299388114_195386_6397476_n[1]12324_112650752089202_106328299388114_194867_5136680_n[1]12324_112736532080624_106328299388114_195388_1902471_n[3]12324_112736545413956_106328299388114_195390_1254104_n[1]

Mar 19, 2011

Call a Web Service Action (Advanced SharePoint Designer 2010 Workflow Actions)

With this custom SharePoint Designer Action you are able to call web services from SPD workflows. The action is included in my Advanced SharePoint Designer 2010 Workflow Actions release on CodePlex. The source code is available too. Let’s go!

1. Add “Call a Web Service” Action to a SharePoint Designer 2010 Workflow

image

2. Configure the Action

image
  • URL: The URL of the Web Service
  • SOAP Version: The SOAP Version of the request (1.1. or 1.2)
  • Action: The SOAP Action for the request
  • SOAP Request Envelope: The SOAP Envelope for the request (http://schemas.xmlsoap.org/soap/envelope/). The request will be UTF8 encoded. You can use two special tokens in the request that can not be inserted via the UI: [WebUrl] and [WorkflowInstanceId].
  • User: The user name for requests that needs authentication e.g. “Contoso\Administrator”. Instead of using user name and password you can use Secure Store Credentials.
  • Password: The password for the user. Unfortunately there isn’t a masked text box :-( You could either specify the password  in clear text or encrypted like described here: Encrypt Passwords for SharePoint Designer Workflow Actions
  • AppId: The The Secure Store Application ID. The Secure Store App have to define 2 Fields. One of type “User Name” and one of type “Password”. The Field Name doesn’t matter, important is the Field Type! You have to map the credentials of the SharePoint Service Accounts (AppPool/owstimer.exe). The secure store option will only works with SharePoint Server not with Foundation!

3. Example

Create a new List with the SharePoint Lists.asmx Web Service.
image

Configuration of “Call Web Service”

  • URL: http://[siteurl]/_vti_bin/Lists.asmx
  • SOAP Version: SOAP 1.2
  • SOAPAction: not needed for SOAP 1.2
  • Response: The response will be stored in the variable “Response”
  • User: empty
  • Password: empty
  • Secure Store AppId: Instead of using user name and password we use the secure store app with the ID test. The app defines “User Name” and Password. The format of the user name for windows user should be “Domain\username”.
  • SOAP Request Envelope:
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<AddList xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<listName>[%MyList:Title%]</listName>
<description>Created by iLSP action</description>
<templateID>100</templateID>
</AddList>
</soap12:Body>
</soap12:Envelope>
For the title of the list we use the title of the current list item that runs the workflow. To figure out how the request should looks like you could inspect request with fiddler. I often create a WCF service proxy with Visual Studio, execute the call and inspect the request and result XML with fiddler.

Next we extract the ID of the new list.from the resulting XML. To do this you can use the “Query XML” action that is also included in the package.

Configuration of “Query XML”

  • XML: The XML of the SOAP-Reponse that has been stored in the Variabe “Response”
  • XPath: //*[local-name()='List']/@ID
  • Output: The output (List Id) will be stored in the Variable “ListID”

Write the “ListID” to the history log

image
download | documentation