SharePoint Bug with MultiValue Fields

SharePoint Bug with MultiValue Fields

imageImagine you have a list (or document library) which has lots of items. Nothing fancy here. Now add a lookup or user field and allow it to contain multiple values.

Inside a view for this list you can filter e.g. for the title column. The filter dropdown shows all possible values for the column. image

 

 

 

 

 

Here comes the clue. If the item count of the list reaches a number somewhere between 400 and 500 items, the filter dropdown changes. You will get an option to show all filter values. This is for performance reasons.

image

Clicking on the “Show Filter Choices” should bring up the filter dropdown like you see it above. Instead you will get a <!–#RENDER FAILED–>.

image

Why that? It looks like the SQL query is broken, because you get this error in the application log.

5586 Unknown SQL Exception 4104 occured. UserData.tp_ID SELECT DISTINCT

The ULS Log will show an entry like this:

System.Data.SqlClient.SqlException: The multi-part identifier "UserData.tp_ID" could not be bound. ORDER BY items must appear in the select list if SELECT DISTINCT is specified. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior)

SqlError: ‘The multi-part identifier "UserData.tp_ID" could not be bound.’ Source: ‘.Net SqlClient Data Provider’ Number: 4104 State: 1 Class: 16 Procedure: ” LineNumber: 1 Server: ‘vmmoss’

10/22/2008 19:54:48.36 w3wp.exe (0x0BD4) 0x1178 Windows SharePoint Services Database 880j High SqlError: ‘ORDER BY items must appear in the select list if SELECT DISTINCT is specified.’ Source: ‘.Net SqlClient Data Provider’ Number: 145 State: 1 Class: 15 Procedure: ” LineNumber: 1 Server: ‘vmmoss’

10/22/2008 19:54:48.36 w3wp.exe (0x0BD4) 0x1178 Windows SharePoint Services Database 5586 Critical Unknown SQL Exception 4104 occured. Additional error information from SQL Server is included below. The multi-part identifier "UserData.tp_ID" could not be bound. ORDER BY items must appear in the select list if SELECT DISTINCT is specified…

There are some KB articles around the RENDER FAILED problem:

Update 24.10.2008

After communicating with Microsoft, it has been confirmed that the behavior is a bug. The really bad news is, that there will be no fix for this version of SharePoint!

Here is an answer from Microsoft:

Fix request has been rejected because of the following reasons:

1. The 500 unique item limit was picked as a threshold to switch from the V3 style filter menus to the V2 menus because above 500 unique items, the performance of the V3 filter menus degrades.

2. I think we can’t have a specific error msg for this error, because this is a general sql query execute error.

Proposed Workarounds are:

List all known workarounds:

W1: edit the ows.js in …\…\12 remove the line var L_FilterMode_Text="Show Filter Choices" reboot the server;and now the option ‘Show Filter Choices’ is no more available.

W2: Reducing the number of fields to a number inferior to 500

If there is any news or if I find another way, I will let you know.

Update:

The problem is fixed with the WSS Cumulative Update from June 30, 2009. Thank you Microsoft.

You have a SharePoint list that has more than 500 items. The list has a column of "People and Groups" type with the "Allow multiple selections" setting set to "Yes". If you click Show Filter Choices in this list, you receive the following error message:

#RENDER FAILED

11 Comments

Thank you

Reply

You have explained my problem exactly. 550 items and you get the error, 499 and it shows the choices.
Shame to hear Microsoft won’t fix it, even if it would be just to have a better error message.

 

Stephen Zweig

Reply

This patches fix the problem

February Cumulative Update Uber Package for Windows SharePoint Services 3.0
(Version: 12.0.6341.5000)
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=961755

February Cumulative Update Uber Package for Microsoft Office SharePoint
Server 2007 (Version: 12.0.6341.5002)
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=961756

 

René Hézser

Reply

Hi Stephen,
the problem still exists with the mentioned patches installed. As mentioned above, MS will not fix the bug in this SharePoint version.

René

 

Mihaela

Reply

Hi,
I have a similar problem in WSS 3.0.
In a custom list with over 2000 items we created 2 different views. In one of the views we get the error message when we try “Show filter choices”. However, for the second view the “Show filter choices” works just fine. Both views display over 1000 records. The only difference is one view contains a filter and the other one doesn’t.
Regards, Mihaela

 

Don

Reply

We have the August 2009 Cumulative patch (KB973400) installed and we still have the error.

 

René Hézser

Reply

Hi Don,
I’m sorry to hear that.
Maybe it is another problem. So I guess the only option you have, is to contact Microsoft about this 🙁

René

 

Amutha

Reply

Dear all, I had the same problem. This was due to the particular column has exceeded the filter limit capability. I tried filtering different column that can still be filtered. Then I proceeded to filter the column which was having issue earlier.

My problem solved this way. I think Microsoft Sharepoint should increase the limit for the filter option.

 

Suresh

Reply

when i tried to install the hot fixes from the below link, it say no products has been updated.please advise whether this will work for 32bit OS?
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=961756

Thanks,
Suresh.

 

Phil

Reply

This fixed our SharePoint 2007 “RENDER FAILED” issue on a list with over 5000 items. Thanks!

 

Paulsur

Reply

Is there any official microsoft documentation published for the unique item filter? I have found this article and one other that mention a limite before you get the “show fileter choice” message. I am looking from Microsoft that describes this action and what triggers it.

 

Søren Bjerre

Reply

Hi don (and others) The fix will only work if the lookup column is NOT indexed (Not checked in the Indexed Columns page) This is a setting on the List Settings page

 

Leave a Reply