partitioning column was not found

Question

Post reply

partitioning column was not found

Jimmy Jen

SSCrazy

Points: 2915
More actions
July 22, 2005 at 8:12 am

#86866

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.
July 25, 2005 at 8:00 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hello,

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:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp

I remember getting similar error, but again, after I made sure all rules are followed exectly, it worked.

Yelena

Regards,Yelena Varsha

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 2

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 ?

Thanks...Jimmy

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Jimmy,

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.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag03/html/PartitioningTips2.asp

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 1

UNION ALL view 'Orders' is not updatable because

a 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')

Regards,Yelena Varsha

Mister Sachmo SSCommitted Points: 1618 More actions · Answer 4

This still seems to be the case in 2008. The CHECK CONSTRAINT on the underlying tables must be set to "Check Existing Data on Creation or Re-Enabling = Yes." I couldn't find this anywhere in BOL, but once I changed the code below, I was no longer getting the partitioning column error.

Old Code:

ALTER TABLE [dbo].[d5match9] WITH NOCHECK ADD CONSTRAINT [CK_d5Match9_PartID] CHECK (([PartID]=(9)))

New Code:

ALTER TABLE [dbo].[d5match9] WITH CHECK ADD CONSTRAINT [CK_d5Match9_PartID] CHECK (([PartID]=(9)))