Microsoft KB Archive/270013

= PRB: Cannot Update Distributed Partitioned Views When You Alter Base Table Constraints =

Article ID: 270013

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q270013



SYMPTOMS
A distributed partitioned view joins horizontally partitioned data from a set of member tables across one or more SQL Servers, which makes the data appear as if it is from one table. If you run any statements that modify the data in the partitioned view after you first disable and then enable the check constraint on any of the involved base tables, you may receive the following error message:

Msg 4436, Level 16, State 12, Line 1

UNION ALL view 'v1' is not updatable because a partitioning column was not found.



CAUSE
When you disable a check constraint, SQL Server marks the constraint as untrusted, which means that the server does not check for consistency when a user modifies rows that may violate the check constraint.

Later, when this check constraint is enabled, SQL Server maintains its status as untrusted because it cannot validate the data for that column. As a result, the Query Optimizer does not use these constraints to prune out unwanted branches in the partitioned view or treat such columns as range partitioned columns. This behavior is by design.



WORKAROUND
To work around this problem, drop and then re-create the check constraint that was altered. You can use the following query to identify the untrusted check contraints in a database:

SELECT name, status FROM sysobjects WHERE xtype = 'C' AND status &0x800 = 0x800



Steps to Reproduce Behavior
  On Computer 1, in the Query Analyzer window, paste the following code: USE pubs GO DROP TABLE t1 GO CREATE TABLE t1 (c_int int NOT NULL) GO CREATE UNIQUE CLUSTERED INDEX i1 ON t1(c_int ASC) GO ALTER TABLE t1 ADD CONSTRAINT c1 PRIMARY KEY (c_int) ALTER TABLE t1 ADD CONSTRAINT c2 CHECK (c_int >= 1000) GO   On Computer 2, in the Query Analyzer window, paste the following code: USE pubs GO DROP TABLE t1 GO CREATE TABLE t1 (c_int int NOT NULL) GO CREATE UNIQUE CLUSTERED INDEX i1 ON t1(c_int DESC) go ALTER TABLE t1 ADD CONSTRAINT c1 PRIMARY KEY (c_int) GO ALTER TABLE t1 ADD CONSTRAINT c2 CHECK (c_int < 1000) GO   On Computer 1, paste the following code: -- Create the View Definition. USE pubs GO DROP VIEW v1 GO CREATE VIEW v1 AS  SELECT * FROM Computer2.pubs.dbo.t1   UNION ALL SELECT * FROM pubs.dbo.t1 GO

SET XACT_ABORT ON GO

-- The following INSERTs complete successfully. INSERT INTO v1 (c_int) SELECT 1 INSERT INTO v1 (c_int) SELECT 1100 GO

-- The following UPDATE completes successfully. UPDATE v1 SET c_int = c_int GO

-- Disable the check constraint. ALTER TABLE t1 NOCHECK CONSTRAINT c2 GO

-- Enable the check constraint. ALTER TABLE t1 CHECK CONSTRAINT c2 GO

-- The following statements fail: UPDATE v1 SET c_int = c_int DELETE FROM v1 GO

-- Drop and re-create the Check Constraints. ALTER TABLE t1 DROP CONSTRAINT c2 ALTER TABLE t1 ADD CONSTRAINT c2 CHECK (c_int >= 1000)

-- From this point forward, all of the DML statements work fine. UPDATE v1 SET c_int = c_int 

Additional query words: error 4436

Keywords: kbprb KB270013

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.