We came across a strange error the other day, reported by a user in SharePoint 2010. When the user attempted to add a new item to a Contacts list, the page threw an error. When we debugged it on the server and showed the server error, it was basically as follows:
with stack trace containing:
We looked in the ULS logs and found the following 2 errors:
- No XsltListViewWebPart was found on this page[/path/siteColl/site/Lists/ListName/NewForm.aspx?IsDlg=1]. Hiding key filters and downgrading tree functionality
- Cannot insert the value NULL into column ‘tp_DocId’, table ‘ContentDBName.dbo.AllUserData’; column does not allow nulls. INSERT fails.
From reading the blog that led me to the answer (below) there may also be an event log error, number 5586 (Unknown SQL Exception 515 occurred).
So I found a blog by Allen Wang about a similar problem he had with a survey. SP2010 Survey List Error and Event ID 5586. He explained in his post that when SharePoint owners add a certain number of columns to their List/Library, all of the same data type, that the SQL storage starts implementing what is called row wrapping. So internally in the database, one of the columns is tp_RowOrdinal (NEVER EVER EVER EVER modify the DB directly), and this number gets increased once you go past the row wrapping limits (see below for PowerShell commands to see this). Here’s a summary of the data types:
Single Line of Text: Wraps after 64 columns
Mult Lines of Text: Wraps after 32 columns
Choice: Wraps after 64 columns
Number: Wraps after 12 columns
Currency: Wraps after 12 columns
Date/Time: Wraps after 8 columns
Lookup: Wraps after 16 columns
Boolean (Yes/No): Wraps after 16 columns
Person/Group: Wraps after 16 columns
Hyperlink/Picture: Wraps after 32 columns
Calculated: Wraps after 8 columns
GUID: Wraps after each and every column
Int: Wraps after 16 columns
Managed Metadata: not sure, seems to be 4 or 6
Please reference this post from Microsoft (I know it’s 2013, but 2010 doesn’t seem to be around any more): Software boundaries and limits.
So what had happened in our case was that there were over 78 columns in the list. When the list Owner modified the column order to move the newly added columns to the top of the Edit/New form, that’s when it broke. That was because RowOrdinal=1 was placed above RowOrdinal=0.
To see what I’m talking about without digging in the database, use PowerShell:
$web = Get-SPWeb https://yourURL.com/path/siteCollection/site
$list = $web.Lists["Name Of Your List"]
$field = $list.Fields.GetField("Field Display Name")
This will output something like this
<Field Type="Number" DisplayName="Field Display Name"
ColName="float10" RowOrdinal="0" Version="1"/>
If you get the SchemaXml of the last few columns, you might see RowOrdinal=”1″ inside the SchemaXml. If you see that, you cannot place that column above another column of the same type which has a 0 ordinal. So for example this would be OK:
but this would break:
I just tried to replicate this in my VM of SP 2013 and I added over 500 number columns and over 100 user columns and was unable to get RowOrdinal to be anything but 0. Not sure why.
Thanks to Allen Wang for providing solution.