Using paging but getting the incoming request has too many parameters error.

[kenyhua]

We implemented paging in order to fix this specific error the first time around. It had been a while since then so I believe it worked partially. I am unsure if it is my code or something internal now throwing this error.

We are using AsyncPoco 2.0.0.

`public async Task<List> GetByTableFieldAndValueNameAsync(int fileDataId, string tableName, string fieldName, string valueName) {
var events = new List(); var eventObjects = new List(); var i = 0; var j = 0; Page eventsPage = null; Page eventObjectsPage = null;

do
{
    i++;

    eventsPage = await DataFactory.Db.PageAsync<Event>(i, 500,
        @"SELECT f.UID1,
                f.UID2,
                a.EventId,
                a.UserName,
                a.EventDate
        FROM [dbo].[FileTable] (NOLOCK) f
        INNER JOIN [dbo].[Event] (NOLOCK) a
            ON a.UID1 = f.UID1
        WHERE a.UID1 = @0",
        uid1);

    events.AddRange(eventsPage.Items);

    do
    {
        j++;

        eventObjectsPage = await DataFactory.Db.PageAsync<EventObject>(j, 500,
            @"SELECT LOWER(eot.TableName) [TableName],
                    LOWER(eot.FieldName) [FieldName],
                    PrimaryKeyID,
                    NewValue,
                    EventId
        FROM [dbo].[EventObjectTable] (NOLOCK) eot
        WHERE eot.EventId IN (@0) 
            AND eot.TableName = @1
            AND eot.FieldName = @2
            AND eot.NewValue = @3",
            events.Select(x => x.EventId).Distinct(),
            tableName, fieldName, valueName);

        eventObjects.AddRange(eventObjectsPage.Items);

    } while (j < eventObjectsPage.TotalPages);

    foreach (var event in events)
    {
        event.eventObjects =
            eventObjects
                .Where(x => x.EventId == event.EventId)
                .ToList();
    }

} while (i < eventsPage.TotalPages);

return events;

}`

Thanks!

[tmenier]

Sorry for the delay. I'm afraid I'm not following this at all. If you believe you've found a bug, please reduce this to most simple, minimal example that reproduces it as you are able to. And please provide all exception details and a stack trace. I can't tell where the bug is occurring from what you've provided.

[kenyhua]

Hi, Sorry for the delay on my end as well. This doesn't happen very often so I was waiting for another one to crop up.

Here is a stack trace:

System.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 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, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
   at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
   at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at AsyncPoco.Database.<ExecuteScalarAsync>d__38`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at AsyncPoco.Database.<PageAsync>d__43`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Afc.Domain.Byte.Provider.AuditLogEventProvider.<GetByTableAndFieldNameAsync>d__8.MoveNext() in C:\TFS\Business Processing Agents\BP-BuildAgent1\_work\596\s\Byte\Provider\AuditLogEventProvider.cs:line 259
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at Afc.Strategy.General.AuditLogEvent.GetAuditLogEventsByTableFieldNameStrategy.<InnerExecuteAsync>d__2.MoveNext() in C:\TFS\Business Processing Agents\BP-BuildAgent1\_work\586\s\General\AuditLogEvent\GetAuditLogEventsByTableFieldNameStrategy.cs:line 25
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Afc.Pattern.Behavioral.Strategy.BaseAsyncGenericStrategyWithParam`2.<ExecuteAsync>d__3.MoveNext() in C:\TFS\Business Processing Agents\Agent 2\_workitems\66\s\Behavioral\Strategy\BaseAsyncGenericStrategy.cs:line 65
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Afc.NServiceBus.Handler.General.AuditLogEvent.GetAuditLogEvents.<Handle>d__5.MoveNext() in C:\TFS\Business Processing Agents\BP-BuildAgent1\_work\606\s\General\AuditLogEvent\GetAuditLogEventsHandler.cs:line 45
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.LoadHandlersConnector.<Invoke>d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Pipeline\Incoming\LoadHandlersConnector.cs:line 42
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.DeserializeLogicalMessagesConnector.<Invoke>d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Pipeline\Incoming\DeserializeLogicalMessagesConnector.cs:line 33
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at ReceivePerformanceDiagnosticsBehavior.<Invoke>d__0.MoveNext() in C:\BuildAgent\work\62a1a7ae466302a5\src\NServiceBus.Metrics\ProbeBuilders\ReceivePerformanceDiagnosticsBehavior.cs:line 23
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.InvokeAuditPipelineBehavior.<Invoke>d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Audit\InvokeAuditPipelineBehavior.cs:line 20
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.SubscriptionReceiverBehavior.<Invoke>d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Routing\MessageDrivenSubscriptions\SubscriptionReceiverBehavior.cs:line 29
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.ReceivePerformanceDiagnosticsBehavior.<Invoke>d__2.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Performance\Statistics\ReceivePerformanceDiagnosticsBehavior.cs:line 40
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.ProcessingStatisticsBehavior.<Invoke>d__0.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Performance\Statistics\ProcessingStatisticsBehavior.cs:line 27
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.TransportReceiveToPhysicalMessageProcessingConnector.<Invoke>d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Pipeline\Incoming\TransportReceiveToPhysicalMessageProcessingConnector.cs:line 39
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.MainPipelineExecutor.<Invoke>d__1.MoveNext() in C:\BuildAgent\work\a93f853f0c1b9532\src\NServiceBus.Core\Pipeline\MainPipelineExecutor.cs:line 34
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.Transport.SQLServer.ReceiveStrategy.<TryProcessingMessage>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at NServiceBus.Transport.SQLServer.ProcessWithTransactionScope.<TryProcess>d__3.MoveNext()
ClientConnectionId:391c2bb0-543b-46d8-ac47-537116330910
Error Number:8003,State:1,Class:16

And the specific code (scrubbed a bit):

DataFactory.Db.PageAsync<AuditLogDataMod>(j, 500,
                    @"SELECT LOWER(adm.TableName) [TableName],
                            LOWER(adm.FieldName) [FieldName],
                            PrimaryKeyID,
                            NewValue,
                            AuditLogEventId
                    FROM [Redacted].[dbo].[AuditLogDataMod] (NOLOCK) adm
                    WHERE adm.AuditLogEventID IN (@0) 
                    AND adm.TableName = @1
                    AND adm.FieldName = @2",
                        auditLogEvents.Select(x => x.AuditLogEventId).Distinct(),
                        tableName, fieldName);

The query on our data for only came back with 2 rows.

[tmenier]

The problem is here:

WHERE adm.AuditLogEventID IN (@0) 

AsyncPoco is going to replace that @0 with a list of SQL parameters, one for each AuditLogEventId in your collection. As the error states, "The server supports a maximum of 2100 parameters", so you're getting this error in cases where that collection exceeds that count. SQL has no native notion of passing a collection as a single parameter, so what you have here is the equivalent of, say, a method with thousands of arguments in its signature.

So this is a SQL Server limitation, not an AsyncPoco bug. You'll need to rewrite this to somehow avoid that scenario. You could maybe use a temp table or something, or filter the results in memory after they come back.

[kenyhua]

Thanks for your help!