Archive

Kategorien

Links

Andere Blogs




ITaCS GmbH


SharePoint Bug with MultiValue Fields  

Oct 222008

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

 
Posted by René Hézser | 11  Comments | Trackback Url  | 0  Links to this post | Bookmark this post with:        
Tags: SharePoint, SPField
Technorati Tags: ,

Links to this post

Comments

commented on  Monday, November 17, 2008  3:32 PM  by  Thank you
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.

commented on  Monday, March 16, 2009  4:59 PM  by  Stephen Zweig
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

commented on  Tuesday, March 17, 2009  10:14 AM  by  René Hézser
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é

commented on  Tuesday, March 24, 2009  8:57 PM  by  Mihaela
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 <!--#Render failed --> 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

commented on  Thursday, December 17, 2009  10:55 PM  by  Don
We have the August 2009 Cumulative patch (KB973400) installed and we still have the error.

commented on  Saturday, February 06, 2010  6:55 PM  by  René Hézser
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é

commented on  Friday, March 26, 2010  8:48 AM  by  Amutha
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.

commented on  Monday, July 19, 2010  9:29 AM  by  Suresh
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.

commented on  Thursday, December 16, 2010  5:02 PM  by  Phil
This fixed our SharePoint 2007 "RENDER FAILED" issue on a list with over 5000 items. Thanks!

commented on  Monday, June 11, 2012  3:48 PM  by  Paulsur
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.

commented on  Wednesday, September 19, 2012  12:26 PM  by  Søren Bjerre
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

Name *:
URL:
Email:
Kommentar:


CAPTCHA Image Validation