Introduction
The article explains how to delete an affected field/column when users are not able to create/edit items in the List/Library.
Why SQL Query?
I know running any queries on SQL was not preferred by Microsoft, but if you notice the below logs error, it's clearly pointing the error to SQL Exception. Trust me, we are not changing anything on the SQL tables by running the below query, the below query we are using is only to find the culprit column/field because of where the users are not able to create/edit items in the list/library.
Problem Description
Recently, we had an issue in Pages Library in one of our sites where the users were not able to create or edit items in the pages library upon trying. We found so many ways in the ULS logs that System.Data.SqlClient.SqlException (0x80131904): The XML content that is supplied for the sparse column set 'tp_ColumnSet' contains duplicate references to the column 'nvarchar10'. A column can only be referenced once in XML content supplied to a sparse column set.
SharePoint ULS Log Error
System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> Microsoft.SharePoint.SPException: The URL 'Test_Pages/Testing.aspx' is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web. ---> System.Runtime.InteropServices.COMException: The URL 'Test_Pages/Testing.aspx' is invalid. It may refer to a nonexistent file or folder, or refer to a valid file or folder that is not in the current Web.
System.Data.SqlClient.SqlException (0x80131904): The XML content that is supplied for the sparse column set 'tp_ColumnSet' contains duplicate references to the column 'nvarchar10'. A column can only be referenced once in XML content supplied to a sparse column set.
The first error in the image is while creating an item in the pages library.
The second error in the image is while editing existing items in the pages library.
Solution
Open SQL Server where your SharePoint Content Databases are hosted and open SQL Server Management Studio (SSMS) and open the New Query for Site Collection hosted database and run the below query by placing your list ID. To get the list ID, go to List Settings from the UI and follow the below steps.
Copy the URL from the browser address bar into Notepad. It will look something like below:
URL
https://contoso.sharepoint.com/sites/test/test01/_layouts/15/listedit.aspx?List=%7BEDAFD060%2DA50D%2D4A0A%2DB716%2D1E50CF232539%7D
Delete everything before and including “List=”
You will be left with:
%7BEDAFD060%2DA50D%2D4A0A%2DB716%2D1E50CF232539%7D
Now put this into a text editor such as notepad use find replace to
Change “%7B” to “{”
Change all “%2D” to “-“
Chnage “%7D” to “}”
You are now left with the ID:
{EDAFD060-A50D-4A0A-B716-1E50CF232539}
Please use the following query to retrieve the tp_CoulmnSet value of items in your list / library. Check the tp_ColumnSet value of the old page and find out what the column “nvarchar10” is. Then check the difference between the column in the old page and that on the new page.
-
- SELECT *
-
-
- FROM [WSS_Content].[dbo].[AllUserData]
-
-
- where [tp_ListId] = '04ADDD01-FCC8-4A33-8444-5DE0D0E78DA8'
-
- And [tp_IsCurrent] = 1
-
The output should be like below, and check for nvarchar10 contains which field from tp_ColumnSet XML, for me I checked for nvarchar10 in XML and I was able to see like this <nvarchar10>aspx</nvarchar10>. Which is pointing to ASPX.
Open the Problematic library in the browser and create a new view for the library and add all the columns in view. In my scenario, I checked for the field which contains ASPX like below.
Conclusion
Upon deleting the problematic field, it fixed our issue. Now I am able to create / Edit columns successfully in the Library.