SP2010 Survey List Error and Event ID 5586

  • Article

Are you experiencing the following combination of errors from event logs and ULS los? I found it occur most often after SharePoint migration from 2007 to 2010. However the same issue can also be reproduced in SP2010 using the out-of-box survey list and a minor change via the standard user interface.

Summary

To save you time, the problem is caused by the first question in the Survey list has a RowOrdinal number other than 0. In another word, the first question in a survey list must have RowOrdinal number equals to 0.

 

First, let’s look at the errors.

Application Event ID 5586:

Unknown SQL Exception 515 occurred. Additional error information from SQL Server is included below.

Cannot insert the value NULL into column 'tp_DocId', table 'SP2010_Content_10030.dbo.AllUserData'; column does not allow nulls. INSERT fails.

The statement has been terminated.

 

SharePoint ULS Log:

MetadataNavigationContext Page_InitComplete: No XsltListViewWebPart was found on this page[/Team/Lists/Survey/NewForm.aspx?IsDlg=1].  Hiding key filters and downgrading tree functionality to legacy ListViewWebPart(v3) level for this list.

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'tp_DocId', table 'SP2010_Content_10030.dbo.AllUserData'; column does not allow nulls. INSERT fails.  The statement has been terminated.   

SqlError: 'Cannot insert the value NULL into column 'tp_DocId', table 'SP2010_Content_10030.dbo.AllUserData'; column does not allow nulls. INSERT fails.'    Source: '.Net SqlClient Data Provider' Number: 515 State: 2 Class: 16 Procedure: 'proc_AddListItem' LineNumber: 475 Server: 'Win2k8-SQL-01'

SqlError: 'The statement has been terminated.'    Source: '.Net SqlClient Data Provider' Number: 3621 State: 0 Class: 0 Procedure: 'proc_AddListItem' LineNumber: 475 Server: 'Win2k8-SQL-01'

Unknown SQL Exception 515 occurred. Additional error information from SQL Server is included below.  Cannot insert the value NULL into column 'tp_DocId', table 'SP2010_Content_10030.dbo.AllUserData'; column does not allow nulls. INSERT fails.  The statement has been terminated.

ConnectionString: 'Data Source=Win2k8-SQL-01;Initial Catalog=SP2010_Content_10030;Integrated Security=True;Enlist=False;Asynchronous Processing=False;Connect Timeout=15'    ConnectionState: Closed ConnectionTimeout: 15

 

To keep this post readable, I’ve omitted the ULS log entry showing the complete SQL query that was executed and failed. You can follow the repro-steps to get the exact SQL query from your environment.

The problem here is because SharePoint utilizes SQL server row wrapping to allow a maximum number of columns in a SharePoint list where SharePoint Server will create several rows in the database when data will not fit on a single row. It then uses a property called RowOrdinal to identify in which row a certain field should be stored. When RowOrdinal = 0, the field is stored in the first row of the row set for that list item and when RowOrdinal = 1, the field is stored in the second row of the row set for that list item, and vice versa.

The number of fields/columns allowed in one single row is based on the type of the fields/columns. SharePoint Server 2010 Software Boundaries and Limits – Column Limits states the following;

Limit

Maximum value

Limit type

Size per column

Notes

Single line of text

276

Threshold

28 bytes

SQL Server row wrapping occurs after each 64 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 384 Single line of text columns per SharePoint list (6 * 64 = 384). However, because the limit per SharePoint list item is 8,000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit is 276 Single line of text columns.

Multiple Lines of Text

192

Threshold

28 bytes

SQL Server row wrapping occurs after each 32 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 192 Multiple lines of text columns per SharePoint list (6 * 32 = 192).

Choice

276

Threshold

28 bytes

SQL Server row wrapping occurs after each 64 columns in a SharePoint list. The default row wrapping value of 6 allows for a maximum of 384 Choice columns per SharePoint list (6 * 64 = 384); ); however because the limit per SharePoint list item is 8,000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit should be 276 Choice columns.

Number

72

Threshold

12 bytes

SQL Server row wrapping occurs after each 12 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 72 Number columns per SharePoint list (6 * 12 = 72).

Note: visit the link for a complete list of column types.

Take Number type for example, row wrapping occurs after each 12 columns in a SharePoint list as stated above. This means the 13th Number typed column you created, would have a RowOrdinal = 1 (25th Number typed column would then have RowOrdinal = 2).

Repro Steps

With the above information, we can reproduce the problem as below

  1. Create a survey list.
  2. Create 13 questions all based on Number type.
  3. Answer the survey by responding all 13 questions. This should complete without any error.
  4. Go to the list settings, change the order of 13th question to 1. (so it becomes the first question of the survey list)
  5. Try answering the survey again by responding all 13 questions.
    Bam! The SharePoint dialog error hits you in the face!

In case you still don’t know what’s going on. This is because the 13th question has RowOrdinal equals to 1 which is not allowed by SharePoint.

Remediation for Existing Survey List

For existing survey lists, the idea is to figure out which questions have RowOrdinal = 0 and then make the one that has RowOrdinal = 0 to be the first question in the survey list.

To find out RowOrdinal number assigned to each of the columns/fields, save the list as a template, eg. Survey.stp. Change the file name to survey.cab, then extract the manifest.xml file in it.

Open manifest.xml and look under ListTemplate > UserLists >List > Metadata > Fields

My Thought on This….

In my opinion, I think it’s more of a bad design issue rather than a bug. I can’t be bothered going into details to capture and analyse SQL Profiler trace, but I did create a custom list and check the list template’s manifest.xml file. With normal a list or library, the first field within <Fields> tag is actually content type ID with RowOrdinal equals to 0. Users cannot change the order of fields within <Fields> tag through SharePoint user interface hence eliminating the possibility of such problem. SharePoint PG certainly needs to review survey list design and come up with a better solution.