SharePoint Bug with MultiValue Fields
Imagine 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. 
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.
Clicking on the “Show Filter Choices” should bring up the filter dropdown like you see it above. Instead you will get a <!–#RENDER FAILED–>.
Why that? It looks like the SQL query is broken, because you get this error in the application log.
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:
- Error message when you try to expand an entry in a Grouped By view in Windows SharePoint Services, in SharePoint Server 2007, or in SharePoint Portal Server 2003: “<!– #RENDER FAILED# –>"
- Error message when you create a view that contains a workflow status filter in Microsoft Windows SharePoint Services V3: “Render Failed”
- You try to render the view of a custom list. When you do this, you may receive the following error message: Render failed
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