Sharepoint 2010 - FullTextSqlQuery RowLimit Hard Limit throws HRESULT: 0x80040E01

Question

Hi All,

I am having a problem with FullTextSqlQuery RowLimit Property. I can not set the property > 10000. Some of the forum talks about a hard limit of 917728059, but my limits hardly reach there before which it throws this error,

Exception thrown was, Exception from HRESULT: 0x80040E01

FullTextSqlQuery .RowLimit: 10001

StackTrace:

   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at Microsoft.Office.Server.Search.Query.FullTextSqlQueryInternal.Execute()
   at Microsoft.Office.Server.Search.Query.QueryInternal.Execute(QueryProperties properties)
   at Microsoft.Office.Server.Search.Administration.SearchServiceApplication.Execute(QueryProperties properties)
   at SyncInvokeExecute(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Exception .Code: {System.ServiceModel.FaultCode}
Exception .SubCode .Name - .SubCode.Name InternalServiceFault (WCF Error)

Some of other forum's members point out that Resource Throttling is not applied to Fulltextsqlquery Search. Also some gives some hint regarding the proportion of returned results. But none of they are not entirely right since I have list of 14k with all 14k items crawled still the limit is 10k as stated above.

Any help would be really appreciated.

Answer 1

Hi All,

I am having a problem with FullTextSqlQuery RowLimit Property. I can not set the property > 10000. Some of the forum talks about a hard limit of 917728059, but my limits hardly reach there before which it throws this error,

Exception thrown was, Exception from HRESULT: 0x80040E01

FullTextSqlQuery .RowLimit: 10001

StackTrace:

   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at Microsoft.Office.Server.Search.Query.FullTextSqlQueryInternal.Execute()
   at Microsoft.Office.Server.Search.Query.QueryInternal.Execute(QueryProperties properties)
   at Microsoft.Office.Server.Search.Administration.SearchServiceApplication.Execute(QueryProperties properties)
   at SyncInvokeExecute(Object , Object[] , Object[] )
   at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
   at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Exception .Code: {System.ServiceModel.FaultCode}
Exception .SubCode .Name - .SubCode.Name InternalServiceFault (WCF Error)

Some of other forum's members point out that Resource Throttling is not applied to Fulltextsqlquery Search. Also some gives some hint regarding the proportion of returned results. But none of they are not entirely right since I have list of 14k with all 14k items crawled still the limit is 10k as stated above.

Any help would be really appreciated.

Further Research - It occurs with WCF call on SearchServiceApplicationProxy. I have checked it in several systems with versions, 14.0.4747 & 4762(Pre-RTM Final).

Answer 2

Hey Do you got any Answer.. as i am also facing same issue.. not able to trace issue.

---

Aswath

Answer 3

This is a change made in SP2010 to force users of the FullTextSqlQuery and KeywordQuery classes to do paging. It has nothing to do with resource throttling, but is a hard coded limitation that only a maximum of 10000 rows will be returned per query. If you want to retrieve more then you must page through them in chunks or 10000 or less. In order to use these classes with this limitation, you must set your TotalRowsExactMinimum property to the number of results you want to page through, in your case 10000. Then set your rowlimit to something smaller than 10001.  You can then read the ResultTable's TotalResults property to determine how many pages based on the rowlimit that you have in your result. Finally, use the FullTextQuerySql classes StartRow property and issue the query again to get the results beyond 10000.  A good explanation in the link below.

http://blogs.msdn.com/b/enterprisesearch/archive/2008/05/22/understanding-total-hits-paging-in-the-moss-2007-search-api.aspx

---

certdev.com

Answer 4

This is a change made in SP2010 to force users of the FullTextSqlQuery and KeywordQuery classes to do paging. It has nothing to do with resource throttling, but is a hard coded limitation that only a maximum of 10000 rows will be returned per query. If you want to retrieve more then you must page through them in chunks or 10000 or less. In order to use these classes with this limitation, you must set your TotalRowsExactMinimum property to the number of results you want to page through, in your case 10000. Then set your rowlimit to something smaller than 10001.  You can then read the ResultTable's TotalResults property to determine how many pages based on the rowlimit that you have in your result. Finally, use the FullTextQuerySql classes StartRow property and issue the query again to get the results beyond 10000.  A good explanation in the link below.

http://blogs.msdn.com/b/enterprisesearch/archive/2008/05/22/understanding-total-hits-paging-in-the-moss-2007-search-api.aspx

---

certdev.com

Hi Steve,

Thanks for the Andrew's Article, I have brushed it through earlier as well. I have tried Paging using StartRow property. The scope I am using, like I said earlier, has more than 14000 entries and the ResultTable .TotalRows gives me that figure.

However, when I set the StartRow to 10001, it hits the same Exception even if the TotalRowsExactMinimum is set to 10000 and RowLimit is set to 10000. Even with StartRow is 9000, it hits the Exception. In otherwords whenever we try to hit the Search DB entries beyond 10000 via any manipulation of StartRow and RowLimit (for Paging) hits the Exception.

To prove this, I tried with RowLimit = 9990 and StartRow = 10, it works for better or worse.

Anyways, this partially gives me the answer since you are certain that Sharepoint 2010 Search has an inbuilt limitation of 10000 for both FullTextSqlQuery and KeyWordSearch classes. But I do have counter question, are you saying that the Custom Enterprise Search limits us to just 10000 rows per hit? Is it all that it can handle?

Could you also please tell me whether we can page (set props) to get the entries beyond 10000? The purpose I have is to find out the total count of entries (which maybe beyond 10000), do you have work arounds (without multiple query executions) apart from TotalRows? I want the items in memory to do some querying since FullText does not support aggregate functions or any calculations within the query.

Answer 5

If you know the query should return 14000 hits, then set your TotalRowsExactMinimum to something larger. This way you can check the ResultTable.TotalRows property. In addition, check the ResultTable.IsTotalRowExact to confirm that it is the exact number of hits. If you set the TotalRowsExactMinimum to something smaller than what you expect, then the TotalRows property will be an estimate, and the IsTotalRowExact property will be false. Once you have set the TotalRowsExactMinimum to lets say 15000, then see if setting the StartRow to 10001 throws an exception.  If this does not work, then I would look at doing something else to accomplish whatever data mining/analysis you are trying to do. 10000 rows is lot to bring back and load into memory and then do calculations on. I would look at Performance Point for some of your needs.

http://technet.microsoft.com/en-us/library/ee661741.aspx

---

certdev.com

Answer 6

Hi,

We can change the limit from 10,000 records (DEFAULT) to higher one (NOTE: you need to calculate as per memory & time for returning query else may end up in memory & timeout issues)

1. Run the following PowerShell to change the limit of the maximum query results, the following set the limit to 20,000.

 

               $ssa = Get-SPEnterpriseSearchServiceApplication

              $ssa.UpdateSetting("Config:qp_MaxResultsReturned", 20000)

                $ssa.Update()

OR

 

2. Through Code:

          SPFarm farm = SPFarm.Local;
           SearchServiceApplication searchApp = (SearchServiceApplication)farm.Services.GetValue<SearchQueryAndSiteSettingsService>().Applications.GetValue<SearchServiceApplication>("Search Service Application");
         searchApp.UpdateSetting("Config:qp_MaxResultsReturned", "20000");

Once we run the code/powershell, do not forget to do IISRESET

 

---

AnjaliCH-MSFT

Answer 7

To Steve Curran: Attempting to page through more than 10000 potential search results in steps smaller than 10000 as you suggest does not appear to work, because setting StartRow above 10000 always raises the 0x80040E01 exception regardless of  the values of RowLimit and TotalRowsExactMinimum.  Do you have a working example of how it can be done?

Answer 8

The problem of the default 10000 maximum is that it is a limit, yes (which can be raised or lowered using the PowerShell commands above) but peculiarly it is also a boundary – a possible oversight by Microsoft. In other words you can happily page through results in pages sizes of less than the maximum using for example:

query.RowLimit = pagesize;  
query.StartRow = pagenumber * pagesize;  
query.TotalRowsExactMinimum = pagesize;

But exception 0x80040E01 is always raised if StartRow exceeds the maximum, regardless of the number of results you wish to retrieve in one step. Therefore you have no choice but to raise the maximum using the PowerShell commands. There appears no way around this.