Need help - view suddenly stopped working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help - view suddenly stopped working

Author

Topic

Munchausen
Starting Member

25 Posts

Posted - 2005-09-02 : 12:19:36

We've got a partitioned table, and use a view to combine them for selects and updates.

The view code is similar to the following:

SELECT * FROM [Table_20M]
UNION ALL
SELECT * FROM [Table_22M]

It used to work fine, but recently, trying to update a record via the view caused this error to pop up:

"UNION ALL view 'Table_View' is not updatable because the definition contains a disallowed construct."

I'm not sure what the problem is. I've looked at the primary key check constraints we have in place (the key can be 1 - 1999999 in the first table, 2000000 - 3999999 in the second table, etc.).

Any ideas what else this error message could indicate?

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 12:54:51

Has someone recently added a new table to the view? That's the first one I would check - the list of "Must do" things when creating horizontal partitions is pretty onerous, and worth double checking

BoL:

The range of values in each member table is enforced by a CHECK constraint on the partitioning column, and ranges cannot overlap.

A view is considered an updatable partitioned view if:

The view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement.

Each individual SELECT statement references one SQL Server base table.

(you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).

The view will not be updatable if a trigger or cascading update or delete is defined on one or more member tables.

Each member table must adhere to these rules:

Member tables cannot be referenced more than once in the view.

Member tables cannot have indexes created on any computed columns.

Member tables must have all PRIMARY KEY constraints on an identical number of columns.

Member tables must have the same ANSI padding setting.

The columns [in the SELECT lists] must follow these rules:

All columns in each member table must be included in the select list. (SELECT * FROM <member table> is acceptable syntax)

Columns cannot be referenced more than once in the select list.

The columns must be in the same ordinal position in the select list

The columns in the select list of each SELECT statement must be of the same type (including data type, precision, scale, and collation).

Partitioning columns must adhere to these rules:

Each base table has a partitioning column whose key values are enforced by CHECK constraints.

The key ranges of the CHECK constraints in each table do not overlap with the ranges of any other table.

Any given value of the partitioning column must map to only one table.

The CHECK constraints can only use these operators: BETWEEN, AND, OR, <, <=, >, >=, =.

The partitioning column cannot be an identity, default or timestamp column.

The partitioning column must be in the same ordinal location in the select list of each SELECT statement in the view.

Partitioning columns cannot allow nulls.

Partitioning columns must be a part of the primary key of the table.

Partitioning columns cannot be computed columns.

There must be only one constraint on the partitioning column.

There are no restrictions on the updatability of the partitioning columns.

Data Modification Rules

You can modify data through a partitioned view only if you install Microsoft SQL Server 2000 Enterprise Edition

INSERT statements must adhere to these rules:

All columns must be included in the INSERT statement even if the column can be NULL in the base table or has a DEFAULT constraint defined in the base table.

The DEFAULT keyword cannot be specified in the VALUES clause of the INSERT statement.

INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.

INSERT statements are not allowed if a member table contains a column with an identity property.

INSERT statements are not allowed if a member table contains a timestamp column.

INSERT statements are not allowed if there is a self-join with the same view or any of the member table.

UPDATE statements must adhere to these rules:

UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause

The value of a column with an identity property cannot be changed: however, the other columns can be updated.

The value of a PRIMARY KEY cannot be changed if the column contains text, image or ntext data.

Updates are not allowed if a base table contains a timestamp column.

Updates are not allowed if there is a self-join with the same view or any of the member tables.

The DEFAULT keyword cannot be specified in the SET clause of the UPDATE statement.

DELETE statements must adhere to this rule

DELETE statements are not allowed if there is a self-join with the same view or any of the member tables.

If distributed views have a few additional requirements

Kristen

SamC
White Water Yakist

3467 Posts

Posted - 2005-09-02 : 13:01:00

Drop the VIEW and ADD it again.

Munchausen
Starting Member

25 Posts

Posted - 2005-09-02 : 13:20:31

quote:
Originally posted by Kristen

The columns [in the SELECT lists] must follow these rules:
All columns in each member table must be included in the select list. (SELECT * FROM <member table> is acceptable syntax)

Columns cannot be referenced more than once in the select list.

The columns must be in the same ordinal position in the select list

The columns in the select list of each SELECT statement must be of the same type (including data type, precision, scale, and collation).

This seems like one of the likely problems. Out of 12 tables (with 2 million rows each), we started to change some of the columns from decimal(18,0) to decimal(18,2). We were working backwards and got tables 11 and 12 done before the view error was noticed. Removing those two tables from the view gets rid of the problem.

So currently, all 12 tables do not have matching scale on those decimal columns.

Thanks for the excellent reference, Kristen. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-02 : 13:20:49

Does the view really have SELECT *?

Tara

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 13:41:40

You're gonna laugh - this is literally what BoL says: