Database upgrade error Episerver CMS 9 to CMS 10

Upgrade from CMS 9 to CMS 10 but you get an error upgrading the database:

Update-EPiDatabase -Verbose:$trueC:\..\packages\EPiServer.Framework.10.10.4\tools\epideploy.exe -a sql -s C:\..\.. -p C:\..\packages\EPiServer.CMS.Core.10.10.4\tools\epiupdates\* -c EPiServerDBFound location C:\..\packages\EPiServer.CMS.Core.10.10.4\tools\epiupdates\config with 0 files of type SqlProcessing C:\..\packages\EPiServer.CMS.Core.10.10.4\tools\epiupdates\sql\7.8.0.sqlScript validation: 0 - Already correct database version...Processing C:\..\packages\EPiServer.CMS.Core.10.10.4\tools\epiupdates\sql\10.3.0.sqlScript validation: 1 - Upgrading databaseRunning script 10.3.0.sqlepideploy.exe :At C:\..\packages\EPiServer.Framework.10.10.4\tools\upgrade.psm1:310 char:3+ &$epiDeployPath -a $action -s $sitePath -p $updatePath\* -c ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:String) [], RemoteException + FullyQualifiedErrorId : NativeCommandErrorEPiDeploy was stopped due to an exception, more details:System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subqueryis used as an expression.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 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.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache,Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at EPiDeploy.Sql.ScriptRunner.<>c__DisplayClass5.<ExecuteScript>b__3() at EPiDeploy.Sql.DatabaseHandler.Execute[T](Func`1 f, Boolean commit) at EPiDeploy.Sql.ScriptRunner.ExecuteScript(StreamReader stream) at EPiDeploy.Sql.ScriptRunner.ExecuteScripts(IEnumerable`1 files, Boolean requiresValidation) at EPiDeploy.Sql.ScriptRunner.<>c__DisplayClass1.<Execute>b__0() at EPiDeploy.Sql.DatabaseHandler.Execute[T](Func`1 f, Boolean commit) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) atEPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Common.Executor.Execute(ILocation location) at EPiDeploy.Deploy.Execute(Options options, ILocation startPosition) at EPiDeploy.Deploy.Run(Options options) at EPiDeploy.Program.Main(String[] args)ClientConnectionId:f553f77b-94d3-4f08-8e44-839bc6eabf00Error Number:512,State:1,Class:16

Suggestion 2:Execute 10.3.0.sql manually and set debug in SQL Management Studio. According to the error message the script should be in this directory C:\..\packages\EPiServer.CMS.Core.10.10.4\tools\epiupdates\sql\.

Steps:1. Open SQL management studio2. Choose File menu3. Choose Open File 4. Choose the file in the directory:(C:\..\packages\EPiServer.CMS.Core.10.10.4\tools\epiupdates\sql\10.3.0.sql)5. Choose your database6. Press Debug button (the one that is next to Execute button).

Conclusion: It seems that the problem is that there are multiple entries in tblScheduledItem for the Episerver tasks such as ‘Automatic Emptying of Trash’ as per below. Tide this up and get back to a single and correct item. Look in the tables tblScheduledItem and tblScheduledItemLog.

Run this query to delete duplicate scheduled jobs (this is an example):delete from tblScheduledItemLog where fkScheduledItemId ='9550334C-CDDA-4222-94A1-60DCB1822CDB'delete from tblScheduledItem where pkID='9550334C-CDDA-4222-94A1-60DCB1822CDB'