Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

partitioning column was not found Expand / Collapse
Author
Message
Posted Friday, July 22, 2005 8:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 22, 2009 1:54 PM
Points: 131, 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.

Post #203537
Posted Monday, July 25, 2005 8:00 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
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!
Post #203970
Posted Monday, July 25, 2005 1:29 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:24 PM
Points: 3,475, Visits: 579

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

Post #204160
Posted Monday, July 25, 2005 3:04 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 22, 2009 1:54 PM
Points: 131, 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

Post #204211
Posted Monday, July 25, 2005 3:38 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:24 PM
Points: 3,475, Visits: 579

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

Post #204225
Posted Monday, November 16, 2009 1:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 25, 2014 1:14 PM
Points: 58, Visits: 135
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)))
Post #819628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse