Partitioned view over tables with a computed column

Hi,
I am having problems with inserting data into a partitioned view that union
a few tables that have a computed column (defined in the tables themselves).
The tables have an identical primary key, partitiong column with a check
constraint.
It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
get the error:
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'partView' failed because it contains a
derived or constant field.
Running the next code on SQL Server 2000 works. However on 2005 the above
error is returned.
Could this an intentional change in partitioned views on 2005 (eventhough
partitioned views exist in 2005 only for backwards compatilibity?...)
This code demonstrate the issue:
create table p1
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
create table p2
(
f1 int,
partColumn int primary key,
CompColumn as f1*2
)
alter table p1 with check
ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
alter table p2 with check
ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
create view partView
as
select * from p1
union all
select * from p2
-- Try to insert new record using the partitioned view:
insert partView (f1, partColumn)
values (1,105)
Any help would be appreticated.
Thanks,
NatyHi
4406 errors were also in SQL 2000, and a way to get around them was to have
an instead of trigger. You don't way which service pack you are on, but it
seems that the product is now more consistent in the way it handles
partitioned views.
John
"Naty" wrote:
> Hi,
> I am having problems with inserting data into a partitioned view that union
> a few tables that have a computed column (defined in the tables themselves).
> The tables have an identical primary key, partitiong column with a check
> constraint.
> It used to work in SQL 2000, but when I use the same schema on SQL 2005, I
> get the error:
> Msg 4406, Level 16, State 1, Line 1
> Update or insert of view or function 'partView' failed because it contains a
> derived or constant field.
> Running the next code on SQL Server 2000 works. However on 2005 the above
> error is returned.
> Could this an intentional change in partitioned views on 2005 (eventhough
> partitioned views exist in 2005 only for backwards compatilibity?...)
>
> This code demonstrate the issue:
> create table p1
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> create table p2
> (
> f1 int,
> partColumn int primary key,
> CompColumn as f1*2
> )
> alter table p1 with check
> ADD CONSTRAINT p1c CHECK ((partColumn >= 0 and partColumn < 100))
> alter table p2 with check
> ADD CONSTRAINT p2c CHECK ((partColumn >= 100 and partColumn < 200))
> create view partView
> as
> select * from p1
> union all
> select * from p2
> -- Try to insert new record using the partitioned view:
> insert partView (f1, partColumn)
> values (1,105)
>
> Any help would be appreticated.
> Thanks,
> Naty
>