Error 'Optimize_For_Sequential_Key' when upgrading a BC140-C/AL to BC220-AL

greys

Hi there!

I'm running through the upgrade steps for migrating a modified C/AL BC140-solution to BC220 (https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/upgrade/devenv-code-conversion). Im stuck at step 6: "Create a new application database for development". In the administration shell of BC220 I run the command "New-NAVApplicationDatabase -DatabaseServer SQLSERVER01 -DatabaseName MyDBforupgrade".

I get the following error: "New-NAVApplicationDatabase : An unexpected error occurred after a database command was cancelled.

On investigating the event viewer I find the following:

DetailedErrorMessage:
SQL error message:
'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE INDEX option.
'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE INDEX option.
'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE INDEX option.
Message: An unexpected error occurred after a database command was cancelled.
StackTrace:
at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ThrowNavSqlException(SqlExceptionAdapter exceptionAdapter, Boolean transactionNoLongerValid, String commandText, Boolean isLastExceptionARollbackCause, Boolean logExceptions, NavCancellationToken cancellationToken)
at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.MapException(SqlExceptionAdapter exceptionAdapter, String commandText, Boolean isRollbackAction, Nullable`1 timeout, NavCancellationToken cancellationToken)
at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.<>c__DisplayClass133_0`1.<ExecuteFunction>b__0()
at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunctionWithTrace[T](EventTask task, Func`1 function, String commandText, SqlCommand sqlCommand, Boolean isAdoCommand, NavCancellationToken cancellationToken)
at Microsoft.Dynamics.Nav.Runtime.NavSqlCommand.ExecuteNonQueryImp(NavCancellationToken cancellationToken)
at Microsoft.Dynamics.Nav.Runtime.InPlacePublishingSchemaCreator.CreateTables()
at Microsoft.Dynamics.Nav.Runtime.TechnicalUpgradeManager.CreateNewApplicationDatabase(String databaseServerName, String databaseName, String databaseUserName, ProtectedDatabasePassword databasePassword)
at Microsoft.Dynamics.Nav.Management.Cmdlets.CreateNavDatabase.InternalProcessRecord()
at Microsoft.Dynamics.Nav.Management.NavCommand.ProcessRecord()
Source: Microsoft.Dynamics.Nav.Ncl"

Has anyone encountered this problem before? Could it be a SQL Server-issue?

Regards,
Greys

mfh

Hi Greys,
Yes, today I am upgrading BC18 to BC23, exactly the same error.
Where you able to solve them?
Would love to hear how :-)
Regards,
Michiel

TheJBird

Sounds like a SQL server issue...

What version of SQL server are you using? Have you set the database version to match the SQL version?

doschulze

We encountered this problem during a BC upgrade from BC14 to BC24.
Our SQL Server version 2016 has provoked the above error.
We were able to work around the error by backing up the database and restoring it to SQL Server version 2019.
The command can then be executed without errors.

The solution idea came from the system requirements of Business Central 24: Here, a SQL Server version 2019 is also installed during the normal installation.
Solution: SQL Server update