SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


partitioning column was not found


partitioning column was not found

Author
Message
Jimmy Jen
Jimmy Jen
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 36

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.


Site Owners
Site Owners
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Administrators
Points: 12521 Visits: 14
No one has responded to this topic yet. Even if you don't have a complete answer, the original poster will appreciate any thoughts you have!
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8520 Visits: 600

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 Varshal

Jimmy Jen
Jimmy Jen
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 36

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
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8520 Visits: 600

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 Varshal

Mister Sachmo
Mister Sachmo
SSC Veteran
SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)

Group: General Forum Members
Points: 280 Visits: 159
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)))
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search