distributed partitioned view pblm

i have the following simple tables and partitioned views ...all is fine
until i try to i/u/d using the distributed part. view...tucker2 and jag are
sqlserver enterprise edition, windows 2k server...
i get ==> union all view 'customers' in not updateable because the defintion
contains
a disallowed construct (42000,4416)
what am i missing' thx, chester
-- On Server1 - tucker2
drop TABLE dbo.Customers_tucker2
CREATE TABLE Customers_tucker2
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 99999),
cust_name varchar(30) NOT NULL,
state_cd char(2) NOT NULL
)
insert into customers_tucker2 values (11101, 'chet1', 'tx')
insert into customers_tucker2 values (11102, 'chet2', 'xx')
insert into customers_tucker2 values (11103, 'chet3', 'tv')
insert into customers_tucker2 values (11104, 'chet4', 'vv')
insert into customers_tucker2 values (11105, 'chet5', 'zz')
-- On Server2 - jag
drop TABLE Customers_jag;
CREATE TABLE Customers_jag
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 100000 AND 999999),
cust_name varchar(30) NOT NULL,
state_cd char(2) NOT NULL
)
insert into customers_jag values (111001, 'chet41', 'tt')
insert into customers_jag values (111002, 'chet42', 'rr')
insert into customers_jag values (111003, 'chet43', 'dd')
insert into customers_jag values (111004, 'chet44', 'gg')
insert into customers_jag values (111005, 'chet45', 'ff')
/*************************************
run the following on both servers
************************************/
drop view customers
go
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
select top 2 * from jag.pubs.dbo.customers_jag
select top 2 * from tucker2.pubs.dbo.customers_tucker2
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
SET XACT_ABORT ON
go
create view customers
as
select customerid, cust_name, state_cd
from tucker2.pubs.dbo.customers_tucker2
union all
select customerid, cust_name, state_cd
from jag.pubs.dbo.customers_jag
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
select min(customerid), max(customerid) from customers
select * from customers
where customerid in (111005,11101)
/* the following 3 update/insert/del fail with msg:
union all view 'customers' in not updateable because the defintion contains
a disallowed construct (42000,4416)
*/
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
SET XACT_ABORT ON
update customers
set state_cd = 'la'
where customerid = 11101
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
insert into customers values ( 1122, 'chet', 'tx')
set ansi_nulls, ansi_warnings, ANSI_PADDING ON
go
SET XACT_ABORT ON
delete from customers
where customerid = 11101Chet,
what for version of sql server are you using? Only the Developer and
Enterprise Editions of SQL Server 2000 allow INSERT, UPDATE, and DELETE
operations on partitioned views.
hth
Quentin
"chet gwin" <cgwin@.houston.rr.com> wrote in message
news:SyGVa.120630$XV.6674033@.twister.austin.rr.com...
> i have the following simple tables and partitioned views ...all is fine
> until i try to i/u/d using the distributed part. view...tucker2 and jag
are
> sqlserver enterprise edition, windows 2k server...
> i get ==> union all view 'customers' in not updateable because the
defintion
> contains
> a disallowed construct (42000,4416)
> what am i missing' thx, chester
>
> -- On Server1 - tucker2
> drop TABLE dbo.Customers_tucker2
> CREATE TABLE Customers_tucker2
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 1 AND 99999),
> cust_name varchar(30) NOT NULL,
> state_cd char(2) NOT NULL
> )
> insert into customers_tucker2 values (11101, 'chet1', 'tx')
> insert into customers_tucker2 values (11102, 'chet2', 'xx')
> insert into customers_tucker2 values (11103, 'chet3', 'tv')
> insert into customers_tucker2 values (11104, 'chet4', 'vv')
> insert into customers_tucker2 values (11105, 'chet5', 'zz')
>
> -- On Server2 - jag
> drop TABLE Customers_jag;
> CREATE TABLE Customers_jag
> (CustomerID INTEGER PRIMARY KEY
> CHECK (CustomerID BETWEEN 100000 AND 999999),
> cust_name varchar(30) NOT NULL,
> state_cd char(2) NOT NULL
> )
>
> insert into customers_jag values (111001, 'chet41', 'tt')
> insert into customers_jag values (111002, 'chet42', 'rr')
> insert into customers_jag values (111003, 'chet43', 'dd')
> insert into customers_jag values (111004, 'chet44', 'gg')
> insert into customers_jag values (111005, 'chet45', 'ff')
> /*************************************
> run the following on both servers
> ************************************/
> drop view customers
> go
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> select top 2 * from jag.pubs.dbo.customers_jag
> select top 2 * from tucker2.pubs.dbo.customers_tucker2
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> SET XACT_ABORT ON
> go
> create view customers
> as
> select customerid, cust_name, state_cd
> from tucker2.pubs.dbo.customers_tucker2
> union all
> select customerid, cust_name, state_cd
> from jag.pubs.dbo.customers_jag
>
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> select min(customerid), max(customerid) from customers
> select * from customers
> where customerid in (111005,11101)
> /* the following 3 update/insert/del fail with msg:
> union all view 'customers' in not updateable because the defintion
contains
> a disallowed construct (42000,4416)
> */
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> SET XACT_ABORT ON
> update customers
> set state_cd = 'la'
> where customerid = 11101
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> insert into customers values ( 1122, 'chet', 'tx')
> set ansi_nulls, ansi_warnings, ANSI_PADDING ON
> go
> SET XACT_ABORT ON
> delete from customers
> where customerid = 11101
>
>sql