SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Need help - view suddenly stopped working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Munchausen
Starting Member

25 Posts

Posted - 09/02/2005 :  12:19:36  Show Profile  Reply with Quote
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?

Edited by - Munchausen on 09/02/2005 12:19:53

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/02/2005 :  12:54:51  Show Profile  Reply with Quote
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
Go to Top of Page

SamC
White Water Yakist

USA
3467 Posts

Posted - 09/02/2005 :  13:01:00  Show Profile  Reply with Quote
Drop the VIEW and ADD it again.
Go to Top of Page

Munchausen
Starting Member

25 Posts

Posted - 09/02/2005 :  13:20:31  Show Profile  Reply with Quote
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. :)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 09/02/2005 :  13:20:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
Does the view really have SELECT *?

Tara
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/02/2005 :  13:41:40  Show Profile  Reply with Quote
You're gonna laugh - this is literally what BoL says:
quote:

(SELECT * FROM <member table> is acceptable syntax)


Kristen
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37291 Posts

Posted - 09/02/2005 :  13:42:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yeah it's acceptable, but it gives problems in views.

Tara
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000