Error while trying to export many entries

[SteveVaneeckhout]

I receive a 500 error when trying to export a large amount of Form entries (2100+)

Reproduction

Bug summary

When trying to export a large amount of entries from a form a server error occurs.

Specifics

Umbraco: 9.5.2
Umbraco Forms: 9.5.0

The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 236
at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
at NPoco.Database.ExecuteReaderHelper(DbCommand cmd)
at NPoco.Database.ExecuteDataReader(DbCommand cmd)

Steps to reproduce

• Create a simple form with 3 fields (2 radiobutton fields and 1 optional textfield).
• Generate 40000 entries.
• Try to export more than 2100 entries at once.

Expected result

An Excel file being downloaded with the form entries

Actual result

Nothing happens in the UI, the loading dots stay visible. When I have the browser dev tools open I notice the 500 Server Error with a call stack in the Network tab.

[AndyButland]

Thanks for letting us know about this. It seems there's some unnecessary paging going on here (for the export, we want to retrieve all results), and that's why it's hitting this limit. Seems to be a longstanding issue, so will resolve for the next patch releases of 8, 9 and 10.

[DrKirk84]

Hey @AndyButland

Yesterday a customer reported that issue. I updated Umbraco Forms to Version 10.1.2 but I am still getting the error:

Microsoft.Data.SqlClient.SqlException (0x80131904): The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 236
at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func1 func) at NPoco.Database.ExecuteReaderHelper(DbCommand cmd) at NPoco.Database.ExecuteDataReader(DbCommand cmd, Boolean sync) ClientConnectionId:53e9ae71-4c62-4cea-bae2-49e156c23322 Error Number:8003,State:1,Class:16

Thanks in advance,
Marc

[AndyButland]

@DrKirk84 - sorry, I've got back to check and it looks like I had a brain fade here and managed to not correctly commit the fix. I'll have to push this now to being fixed in the next patch.

[DrKirk84]

@AndyButland thank's for fixing it. Do you know, when we could expect the next release? Just to inform the customer

[AndyButland]

We are planning the next patch release for 18th October.

[SteveVaneeckhout]

@AndyButland I just tested with 10.1.3 and I still get the same 500 server error:

Microsoft.Data.SqlClient.SqlException (0x80131904): The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 236
   at Umbraco.Cms.Infrastructure.Persistence.FaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at NPoco.Database.ExecuteReaderHelper(DbCommand cmd)
   at NPoco.Database.ExecuteDataReader(DbCommand cmd, Boolean sync)

[AndyButland]

Hmm... in this case I can see at least I didn't make the silly mistake I did last time and omit the commit from the release, I can see the fix is in place for 10.1.3. Are you getting this error coming from the back-office (i.e. clicking the "Export" link when viewing the entries). Or is it from some other route (e.g. from some custom code that's calling the export functionality)?

[SteveVaneeckhout]

I just click the Export link (After setting the date filter to "All Time"). Both "Excel File (submitted values)" and "Excel File (display values)" throw the server error. No custom code, custom export or custom workflow is involved with this website.

[AndyButland]

OK, I see it now. It seems there were actually two fixes needed and I only resolved the one. Sorry about this, fixing this issue has not been my finest hour! I'll make sure this fix is in the RC for 10.2, planned for release on Tuesday. And will make available in the next patches for the older versions.