This is a tad of a messy blog post, so apologies for that, however it’s mainly for my own reference.
Here at Crocus, we run Navision to handle product inventory, orders, and the like…
We wanted to upgrade from version 4, to version 5.
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.
Annoying.
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")
Problem was:
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