SQL upgrade script 4.10 to 4.20 Failed with 'ALTER TABLE ALTER COLUMN SentOnUtc failed'

Trying to upgrade from 3.90 to 4.20 by running the 3.9 to 4.0 script, then 4.0 to 4.1, and then 4.1 to 4.2 script. The first two scripts run with out any errors but the 4.1 to 4.2 script fails with the following error:

(1 row affected)
Msg 5074, Level 16, State 1, Line 2657
The index 'idx_DCh_43_42_QueuedEmail' is dependent on column 'SentOnUtc'.
Msg 4922, Level 16, State 9, Line 2657
ALTER TABLE ALTER COLUMN SentOnUtc failed because one or more objects access this column.


Please help me figure out what I need to do to get it working.

The upgrade instructions say:

----------------------
(upgrading from 3.X to 4.X): If you want to upgrade from a version 3.90 to the latest version, you would need to install 4.00 first (over the existing database), run the 3.90 to 4.00 migration SQL script, and then upgrade to 4.10, 4.20 etc

https://docs.nopcommerce.com/user-guide/installing/upgrading.html
-------------------

If I have an existing database full of customers and data, how do I 'Install v4.0 OVER THE TOP OF THE EXISTING DATABASE without losing all my data?

I have not done it but you can try and see if that is what they mean

Take a backup of the 3.9 Database

Run the Install
Dont select the create database option
Enter the existing database name, etc
Dont select the create Sample Data option

Thanks!

I tried that and when I point the 4.20 installer at the existing 3.90 database, the installer runs but then throws the error:

     "Setup failed: Sequence contains more than one element"

So I go the \App_Data\Settings.txt file from the 3.90 install and put it in the \App_Data\ directory and re-named it ".dataSettings.json" which points to the same database.

However, when I run the installer again, I get the same setup failed message.

Not sure how to proceed installing the v4.2 database over the top of the 3.90 database without losing all my customer info while getting the install script to run without failing.

Man, this is such a pain and the both the install and upgrade process is so poorly documented that I might have to move to wordpress and WooCommerce instead.

The current installation guide is so sad that it doesn't even tell you the path to go to run the installer and none of the upgrade steps that are provided work.

matrix1000 wrote:

Its a bit confusing - I was thinking it meant

You run the 3.9->4.0 script to upgrade database to 4.0
Then run the 4.0 installer on the 4.0 database
- I just checked that this creates dataSettings.json from setting.txt
- Not sure what else it might do
Then you should be able to run the v4.0 website on this database as an interim check
Then continue on with the scripts 4.0->4.1, 4.1->4.2

Thanks!

It wont run the installer because the error basically says that it wants to run the database script but it sees existing tables in the database so it errors out.

RE:  "...The index 'idx_DCh_43_42_QueuedEmail' ..."

That's an unusual index name; not standard nopC naming convention.  3.90 and 4.00  did not have an index on SentOnUtc.  I don't have 4.10 installed thus can't tell you if it does...   I'd say just DROP the index.   4.20 does have index on that column.  If you don't see it after upgrade, then create it:

CREATE NONCLUSTERED INDEX [IX_QueuedEmail_SentOnUtc_DontSendBeforeDateUtc_Extended] ON [dbo].[QueuedEmail]
(
  [SentOnUtc] ASC,
  [DontSendBeforeDateUtc] ASC
)
INCLUDE (   [SentTries])

I had the same error upgrading 4.1 to 4.2:
Msg 5074, Level 16, State 1, Line 2657 The index 'idx_DCh_10139_10138_QueuedEmail'
is dependent on column 'SentOnUtc'. Msg 4922, Level 16, State 9, Line 2657
ALTER TABLE ALTER COLUMN SentOnUtc failed because one or more objects access this column.

Line 2657:
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'QueuedEmail' AND COLUMN_NAME = 'SentOnUtc' and DATA_TYPE = 'datetime')
BEGIN
  DROP INDEX [IX_QueuedEmail_SentOnUtc_DontSendBeforeDateUtc_Extended] ON [QueuedEmail]
  ALTER TABLE [QueuedEmail] ALTER column [SentOnUtc] [datetime2](7) NULL
  CREATE NONCLUSTERED INDEX [IX_QueuedEmail_SentOnUtc_DontSendBeforeDateUtc_Extended] ON [QueuedEmail] ([SentOnUtc], [DontSendBeforeDateUtc]) INCLUDE ([SentTries])
END
GO

Table QueuedEmail - indexes after the fail:
idx_DCh_10139_10138_QueuedEmail
IX_QueuedEmail_CreatedOnUtc
PK__QueuedEm__3214EC071BE49653

So I'm thinking I should drop the idx_DCh_10139_10138_QueuedEmail index as suggested by New York, and create the missing index.