I recently ran a VS TFS Build to the production server. The change was adding a new column to the underlying tables, but not altering the primary key or constraints on those table. Suddenly we were getting the below error on any attempt to insert or update on the view:
UNION ALL view is not updatable because a partitioning column was not found
After much research, testing and banging my head on my keyboard, I eventually found the error was due to the underlying check constraints being no longer trusted. Seems like some hiccup occurred during the publish/build.
The error message was technically correct: partitioning column was not found not because it didn't exist but because the check constraints were no longer trusted.
Borrowed this bit of code I found on Brent Ozar's website to find untrusted check constraints on my database:
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname from sys.check_constraints i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0;And then issued this fix, on all untrusted check constraints, to check them.
ALTER TABLE dbo.TableName WITH CHECK CHECK CONSTRAINT CK_CheckConstraintName;Everything is back to normal now. Phew.
Post a Comment