File Upload Size Limits

Problem: Need to store files in excess of 2GB in SharePoint.
Initial Hypothesis:

• 50MB is the default upload limit set by SharePoint OOTB.  You can change this on the farm as described here by Dave Coleman up to 2GB or 2047MB. 
• A common misconception is that by using RBS and not you content database to store the blob you can overcome this 2GB limit.  We it's partly true...  The maximum file size for a file in SQL Server is 2GB however, the next restraint is SharePoint 2010 Server Object Model and this has a hard limit of 2GB for an upload so moving to RBS won't overcome the problem.
• I believe SharePoint limit's the upload to 2GB due to IIS's worker process w3wp.exe, to upload a file you need to use all the IIS available memory to upload the full stream.  Each w3wp.exe worker process runs well with 2-4GB of memory, this is not a boundary just a good idea (on x64), therefore this makes sense to me that the SP2010 team have limited any file upload to 2GB.
• Also be aware that increasing you upload file size to 2 GB has performance ramifications so it a user uploads a file and there is no memory available no new requests can be handled until the memory is available again.

Resolution:  Store large files outside SharePoint and surface them in SharePoint.  I believe there is another solution available using a Telerik Silverlight upload control but I haven't tried it.
More Info:
http://blah.winsmarts.com/2010-3-Large_File_Upload_in_SharePoint_2010.aspx

Update 24/07/2013: SharePoint 2013 has the same hard limit of 2 GB for the maximum upload size.  Technet states 50 MB is the default limit for SP2013, the default from an OOTB install is 250MB which is the same value you get with SharePoint Online/Office 365.

Corrupt site column cannot be deleted

Problem: I have deployed a site column (field) and Content Type declaratively, the site column is not valid and I get the following error when looking at my site columns using SharePoint 2010's UI: "Field type xxx is not installed properly. Go to the list settings page to delete this field."

Initial Hypothesis:  The cause of the issue is simply that I create a site column of type "Bool", Type bool does not exist, boolean is the correct type, as show below:

Resolution: Delete the Site Column, can't do this I tried SharePoint's UI (as shown in the problem image), SharePoint Designer, Solution Explorer with CKSDev extensions.  All tools error as the Site Column object cannot in instantiated.

So now it was time to try Powershell which will fail as it can't instantiate the Site column object either. 
PS> $web = Get-SPweb http://demo1/sites/sponline
PS> $fields = $web.Fields
PS> foreach($field in $fields) {  write-host $field.Id }

This proves the site column exists but it is corrupted.  I tried deleting it using PS.
Obviously the site column won't be removed. 

Summary:  At this point I am in a knot.  The site column is causing errors and it can't be removed, so the option is to delete the site collection or to do the bad stuff and fix the error directly in the database.

                         =================

Problem:  A corrupt site column needs to be deleted using T-SQL.

Initial Hypothesis: Find the corrupt/offending field, as you can't use SharePoint's API to remove the field, it will need to be done directly in the content database using T-SQL.

Resolution:

1. Find the site column causing the issue using Powershell.
2. I opened the field.text file and found:   I did a search for "bool" in the text document, there are a lot of results but all the other found "boolean".  So simply search for the type that is shown in your error message.
3. Find the content database that is storing the offending Site Column using Central Admin.

4.> Open "Microsoft SQL Server Management Studio" and perform a query to find the offending record

T-SQL:  SELECT * FROM [Demo_PortalDB].[dbo].[ContentTypes] WHERE Definition LIKE ('%332B55548E6C%')
5.> Delete the offending record

T-SQL: DELETE FROM [Demo_PortalDB].[dbo].[ContentTypes] WHERE Definition LIKE ('%332B55548E6C%')

Summary:  Editing SharePoint database directly is not supported by Microsoft and should not be done under any circumstances.  I can't find another way to fix this issue - so if anyone has a suggestions I'd love to know it.

Error Message Examples:
Field type Bool is not installed properly. Go to the list settings page to delete this field.
Field type Financials Gross Value Certified is not installed properly. Go to the list settings page to delete this field.
Field type UKTelephone is not installed properly. Go to the list settings page to delete this field.

SharePoint Retreat South West London

Overview:  Ashraf Islam & I are presenting a SharePoint Retreat on 14 May in Cobham (South West London).  We are looking at LINQ to SharePoint & InfoPath on the day.  This event broadly follows Andrew Woodward's SharePoint Retreat format: so it's free, everyone attending will get real world coding practice and we to retrospectives after each session.  http://www.21apps.com/sharepoint/spretreat-swlondon2011/
To book for the event: http://spretreatswlondon.eventbrite.com/

LINQ to SharePoint Session Downloads:

VS project - create lists & set up referential integrity.

VS project - deploy a visual user control web part via a sandbox solution. 

Slide Deck

Update 14 May 2011 - Thanks to everyone - i gained a lot from the day, good to meet a and discuss things with some passionate devs & architects - paul

Update 15 May 2011 - SPRetreat roundup - http://blog.sharepointsite.co.uk/2011/05/sharepoint-retreat-roundup.html

Update 28 May 2011 - Video I recorded on using LINQ to SharePoint

SP2010 Data Access options for developers

Overview:  This post contains 4 wmv's that explain both client side and server side data access options for developers.  This was a single pesentation that I have broken up into 4 smaller units of 10-15 minutes each is about 15 Megs in size. 

Part 1 - Overview of Data Access options for SharePoint 2010 developers
Part 2 - Introduction to LINQ to SharePoint
Part 3 - More options
Part 4 - LINQ to SharePoint tips

SP2010 lists vs DB tables

Overview:  When developing using the SP2010 Server Object Model you don't have to store data in SharePoint lists, especially considering the BCS in SP2010.  This presentation video was part of a session present on data access options for Sharepoint 2010 in April 2011. It specifically looks are comparing database table vs SharePoint lists to help me decide which option to use. 
http://www.youtube.com/watch?v=8ecYVdR3a1g

Referential Integrity between Lists

Problem: SP2010 adds referential integrity between lists using a lookup column.  I wish to push out cascade referential integrity between my lists using declarative CAML to build and link my 2 SharePoint lists.

Initial Hypothesis:  I am going to work through an example to highlight 2 key concepts when deploying a list declaratively using CAML (Using XML which is CAML I am declaratively creating 2 lists namely: Customers and Orders). 
1) I want to display a project field not the ID column that is doing the binding as my lists will make more sense to users updating them.  As shown below:

2)  Setup Cascade referential integrity between the lists (can't be done on the Site Column Lookup).  Referential integrity is done between list instances not on the Site Column.  Therefore, I use a feature receiver to wire up the referential integrity. The result of setting referential integrity is Shown as it will appear in the UI.

There are 3 ways to create lists namely: UI, programmatically or declaratively.  In MOSS the best option for lookup lists was to do it progratically using feature receivers.  My preferred approach is to use the declarative CAML approach.

Tip: The correct way to create a list is to ensure you have followed the 4 steps in this order using CAML: Site Columns, Content Types, List Definition & List Instance.

Tip: CKSDev is an VSIX extension that you can load into Visual Studio 2010 (VS2010) using Microsoft Gallery, it is a codeplex based project that adds functionality to VS2010.  You can use you "Solution Explorer" in VS2010, navigate to the site columns, right click and generate the CAML to declativtively build Content Types and Site columns.  So you can create the content types thru the UI then generate the CAML and now you have a repeatable mechanism of deploying content types and site columns. 

Resolution:
1) Lookup Field (Lookup Site column) should use the customer name not the default ID for linking (This actually links on the customer "ID" but the link displays the "Customers Name".  This is done on the field in the CAML using the attributes "ShowField" and "DisplayName".

2) The second point and title of this posts is about setting referential integrity between lists.  You cannot use the site column declaritive code to create the referential integrity.  The attribute "RelationshipDeleteBehaviour" is used to set the referential integrity behaviour, this can be either None (Default), Restrict or Cascade at a Site Column level.  Using the attribute "RelationshipDeleteBehaviour" does not work as shown below as referential integrity is done between list instances not on the site column.  Why the attribute "RelationshipDeleteBehaviour" is present I have no idea.

Add a new feature to the VS2010 sandbox solution project (Right Click "Features" in "Solution Explorer" and select "Add Feature")

Add the feature Receiver code (Right Click "Feature2" (or whatever you called it), select "Add Event Receiver".

Add the appropriate code to the feature receiver as shown below:

  

Tip:

"You can apply a cascade delete rule. In this case, if a user deletes an item from a parent list, related items in the child list are also deleted. This helps to prevent orphaned items in the child list.

You can apply a restrict delete rule. In this case, users are prevented from deleting an item that is referenced by items in a related list. This helps to prevent broken lookup links in the data model." MSDN

Download:
Download the Visual Studio 2010 project that builds the 2 SharePoint lists in a sandbox solution and adds referential integrity to the list lookup relationship.

More Info:

http://blog.sharepointsite.co.uk/2010/08/creating-lists-programtically-in-sp2010.html

http://msdn.microsoft.com/en-us/library/ff798514.aspx

http://msdn.microsoft.com/en-us/library/ff798409.aspx

Where are Assemblies in Sandboxed Solutions Deployed?

I found this article on where Sandbox Solutions store their assemblies.   As I didn't know this thought I would share the info.

Assemblies (dll's) are packaged into Sandbox Solutions and deployed into the Solution Gallery of the Site Collection.  Sandbox solutions cannot work with local resources.   When the sandboxed solution is accessed for the 1st time, the wsp (sandbox solution) is unpacked and the dll's are copied to the file system for each server that handles the request to a file location specified on each servers SPUCHostService.exe (User Code Host Service), the default location is C:\ProgramData\Microsoft\SharePoint\UCCache
More Info:
http://blogs.msdn.com/b/sharepointdeveloperdocs/archive/2010/10/06/where-are-assemblies-in-sandboxed-solutions-deployed.aspx