Migrator partial-export can exceed 2100 SQL parameter limitation for large exports of multiple projects

[michaelnoonan]

Using the new abilities shipped in #5129 you can export enough projects to cause subsequent queries to fail. Most of the queries use a SQL Query like SELECT * FROM DeploymentProcess WHERE Id IN (@project1, @project2, ... , @project999999).

We should review each of these queries to find a different style of query requiring less parameters, or batch the queries into sets.

What I expected to happen

All of the projects and their dependencies should export successfully.

Log excerpt

2018-12-05 15:02:45.3498   5124      1 FATAL  Exception occurred while executing a reader for `SELECT *
FROM dbo.[DeploymentProcess]
WHERE ([Id] IN (@id0_161, @id1_162, @id2_163, @id3_164, @id4_165, @id5_166, @id6_167, @id7_168, @id8_169, @id9_170, @id10_171, @id11_172, @id12_173, @id13_174, @id14_175, @id15_176, @id16_177, @id17_178, @id18_179, @id19_180, 
...
@id2830_2991, @id2831_2992, @id2832_2993, @id2833_2994, @id2834_2995, @id2835_2996, @id2836_2997, @id2837_2998, @id2838_2999, @id2839_3000))
ORDER BY [Id]`
System.Exception
   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
   at System.Linq.Enumerable.SelectManyIterator
   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at Octopus.Migrator.Core.ExportProcess.PartialExportDataSource.GetPropertyValuesFromAllActions(String propertyName)
   at Octopus.Migrator.Core.ExportProcess.PartialExportDataSource.GetActionTemplates()
   at Octopus.Migrator.Core.ExportProcess.PartialExportDataSource.GetCommunityActionTemplates()
   at Octopus.Migrator.Core.ExportProcess.ExportController.Export()
   at Octopus.Shared.Startup.AbstractCommand.Start(String[] commandLineArguments, ICommandRuntime commandRuntime, OptionSet commonOptions)
   at Octopus.Shared.Startup.ConsoleHost.Run(Action`1 start, Action shutdown)
   at Octopus.Shared.Startup.OctopusProgram.Run()

--Inner Exception--
SQL Error 8003 - The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
System.Data.SqlClient.SqlException
   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.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)

Affected versions

Octopus Server: 2018.9.13

Workarounds

Exporting smaller batches may alleviate this problem, however importing in batches may cause shared things like Certificates, Accounts, Variable Sets, etc, to end up with the wrong scoping.

[michaelnoonan]

Migrator partial-export command should enable exporting multiple projects

[octoreleasebot]

Release Note: Resolved issue with migrator partial-export command exceeding 2100 SQL parameters

[lock]

This thread has been automatically locked since there has not been any recent activity after it was closed. If you think you've found a related issue, please contact our support team so we can triage your issue, and make sure it's handled appropriately.