This is a tad of a messy blog post, so apologies for that, however it’s mainly for my own reference.
After following the upgrade instructions, I got this error:
Msg 8662, Level 16, State 0, Line 1
Cannot create the clustered index "VSIFTIDX" on view "Nav5.dbo.Crocus Live$OrderRequestDetail$VSIFT$1" because the view references an unknown value (SUM aggregate of nullable expression). Consider referencing only non-nullable values in SUM. ISNULL() may be useful for this.
There wasn’t anything obviously wrong.
So I opened up SQL Profiler, and re-ran the update process in the Navision client, to capture the exact SQL queries that were causing the error….
I saw it created a view called Crocus Live$OrderRequestDetail$VSIFT$1
Heres the create view script:
CREATE VIEW dbo."Crocus Live$OrderRequestDetail$VSIFT$1" WITH SCHEMABINDING AS SELECT "PaidFor",COUNT_BIG(*) "$Cnt",SUM("Amount") "SUM$Amount", SUM("AmountIncludingVAT") "SUM$AmountIncludingVAT" FROM dbo."Crocus Live$OrderRequestDetail" GROUP BY "PaidFor"
It was failing on the following query:
CREATE UNIQUE CLUSTERED INDEX "VSIFTIDX" ON dbo."Crocus Live$OrderRequestDetail$VSIFT$1" ("PaidFor")
Some of the columns specified as “boolean” within Navision, were in fact NULLABLE tinyint columns within SQL Server.
I suspect that this was due to some legacy version of SQL not supporting bit columns, or something along those lines…
Anyway, none of the values in the column (close to a million rows within the table) was null, so I changed the datatype to BIT and set NOT NULL. I did this for each of the columns in the table which should of been a boolean (and were specified in the CREATE VIEW statement (in my case was Amount, AmountIncludingVAT and PaidFor)
This immediately solved the “Cannot create the clustered index” problem