Schemabound View will not Update

[bdc101]

I have a partitioned view that is schemabound to the underlying tables. There is a job that runs every minute that has a step which is supposed to update the view. When the job runs, that step fails with the following error message:

UNION ALL view 'sl' is not updatable because a partitioning column was not found. [SQLSTATE 42000].[Error 4436]. The step failed.

This is the code for the view:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[sl]
with schemabinding
as

select proj_num, barcode, sale_status, previous_call_time, area_code,
zip, fico, tape_num, call_cnt, phone, the_data, blocked, first_call, first_color,
sched_date, spoke_to, sched_name, sched_note, sched_type
from dbo.screen_list_6654 (nolock)
union all
select proj_num, barcode, sale_status, previous_call_time, area_code,
zip, fico, tape_num, call_cnt, phone, the_data, blocked, first_call, first_color,
sched_date, spoke_to, sched_name, sched_note, sched_type
from dbo.screen_list_6656 (nolock)


Any thoughts on what could be causing the problem?

 

[mrdenny]

Which column is your partitioning column? Is it part of the primary key? What constraints are there on that column?

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[bdc101]

The primary key was proj_num and phone. There was a check constraint on the proj_num column to confirm the records from other project numbers do not get entered. I have it working now by removing all the check constraints and the primary key. I know that is not ideal, but it had to work now. Is there a way to get the primary key back on the tables and have the view update?

 

[mrdenny]

The rules for a partitioned view are:

The key ranges of the CHECK constraints in each table cannot overlap with the ranges of any other table. Any specific value of the partitioning column must map to only one table. The CHECK constraints can only use these operators: BETWEEN, IN, 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. For example, the partitioning column is always the first column in each select list or the second column in each select list, and so on.


The partitioning column cannot allow for nulls.


The partitioning column must be a part of the primary key of the table.


The partitioning column cannot be a computed column.


There must be only one constraint on the partitioning column. If there is more than one constraint, SQL Server ignores all the constraints and will not consider them when determining whether the view is a partitioned view.


If any of these rules are not meet then SQL will not allow you to update via the view.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog