I created a partitioned view then I try to insert using INSERT...SELECT and I get the following error even after I drop the CHECK constraint and add it back:
"UNION ALL view 'vwClaims' is not updatable because a partitioning column was not found."
In my case the primary key consists of two columns, the CHECKed column plus the other column.
Any idea why am I geting this error ? Thanks in advance.
My experience with Updatable Distributed views was that you should follow exactly all 5 pages of rules including
XACT_ABORT SET option must be set to ON.Member tables must have the same ANSI padding setting
and everything else at:
I remember getting similar error, but again, after I made sure all rules are followed exectly, it worked.
Thanks for taking the time to reply. I really appreciate it.
In my case, I'm just creating a local partitioned view. Do I still need to turn on XACT_ABORT SET ?
I am not sure abbout XACT_ABORT.
What I would do is to check the check constraints if your data is partitioned properly. See this article, it may help.
a paragraph right above the header "A Trick for INSERTs ".
It says: "
This time, the insert fails, resulting in the following error message:Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Orders' is not updatable becausea partitioning column was not found.
Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Orders' is not updatable becausea partitioning column was not found.
To fix the problem, drop and recreate all CHECK constraints by running the code that Listing 3 shows. Now, when you retry the SELECT and INSERT queries, the partitioned view works as expected. If you use T-SQL to perform future schema changes, you won't have to fix anything.
Listing 3 is as follows (for the particular example in the article):
Listing 3: Drop and Recreate All CHECK Constraints in Orders PartitionsALTER TABLE Orders2002 DROP CONSTRAINT CHK_Orders2002_orderdateALTER TABLE Orders2001 DROP CONSTRAINT CHK_Orders2001_orderdateALTER TABLE Orders2000 DROP CONSTRAINT CHK_Orders2000_orderdateALTER TABLE Orders2002 WITH CHECK -- default ADD CONSTRAINT CHK_Orders2002_orderdate CHECK(orderdate >= '20020101' AND orderdate < '20030101')ALTER TABLE Orders2001 WITH CHECK ADD CONSTRAINT CHK_Orders2001_orderdate CHECK(orderdate >= '20010101' AND orderdate < '20020101')ALTER TABLE Orders2000 WITH CHECK ADD CONSTRAINT CHK_Orders2000_orderdate CHECK(orderdate >= '20000101' AND orderdate < '20010101')