SQL query fails on tenants page

When We trying to open tenants page in UI we got this error:
The incoming request has too many parameters. The server supports a maximum of 2100 parameters.

System.Data.SqlClient.SqlException: 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)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   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 Nevermore.Transient.IDbCommandExtensions.<>c__DisplayClass5_0.<ExecuteReaderWithRetry>b__0()
   at Nevermore.Transient.RetryPolicy.ExecuteAction[TResult](Func`1 func)
   at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy commandRetryPolicy, RetryPolicy connectionRetryPolicy, String operationName)
   --- End of inner exception stack trace ---
   at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, RetryPolicy commandRetryPolicy, RetryPolicy connectionRetryPolicy, String operationName)
   at Nevermore.Transient.IDbCommandExtensions.ExecuteReaderWithRetry(IDbCommand command, String operationName)
   at Nevermore.RelationalTransaction.<Stream>d__40`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Octopus.Server.Orchestration.ServerTasks.Deploy.TenantVariableLoader.GetTenantVariablesByProject(IEnumerable`1 projects, String environmentId)
   at Octopus.Server.Orchestration.ServerTasks.Deploy.TenantVariableLoader.GetTenantVariablesByProject(IEnumerable`1 projects, String environmentId)
   at Octopus.Server.Orchestration.ServerTasks.Deploy.TenantVariableLoader.GetTenantVariables(IEnumerable`1 projects, IEnumerable`1 libraryVariableSets, String environmentId)
   at Octopus.Server.Orchestration.ServerTasks.Deploy.TenantVariableLoader.GetIterator(String tenantId, String projectId, String environmentId)
   at Octopus.Server.Orchestration.ServerTasks.Deploy.TenantVariableLoader.MisingByTenant(String tenantId, String projectId, String environmentId)
   at Octopus.Server.Web.Api.Actions.TenantVariablesMissingAction.Execute()
   at Octopus.Server.Web.Infrastructure.Api.Responder`1.ExecuteRegistered()
   at Octopus.Server.Web.Infrastructure.Api.Responder`1.Respond(TDescriptor options, NancyContext context)
   at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
   at Octopus.Server.Web.Infrastructure.OctopusNancyModule.<>c__DisplayClass14_0.<get_Routes>b__1(Object x)
   at Nancy.Routing.Route.<>c__DisplayClass4.<Wrap>b__3(Object parameters, CancellationToken context)

Octopus version 2018.2.5
When error is occurred we have 4200 projects, to resolve this problem We removed some (to 4149) and page is working now.

Related issue https://github.com/OctopusDeploy/Issues/issues/3742
Discussion: https://help.octopusdeploy.com/discussions/problems/56963-can-not-add-new-environment-to-tenantproject

Current github issue https://github.com/OctopusDeploy/Issues/issues/4309

FYI: @Dmitry_Lobanov

Hi Andrei,

Thanks for getting in touch!

Our modelling team is investigating the issue that you logged and will be able to provide more information shortly,

Regards
Alex

Hi Andrei,

I’ve found the source of this bug. Interestingly, I believe this issue will be hit if the number of projects in your octopus instance is 2099, 2100, or any multiple of either of those two numbers. This explains why you encountered the issue at 4200 projects, but not at 4149 projects.

We will be shipping a fix for this very soon. Please keep an eye on the Github issue to see when the fix will go out.

Regards,
Tom

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.