A strange problem with updatable partitioned view.

Hi all,
I am working on a distributed database. I defined linked
servers, partitioned views etc. I can delete/insert/update
data from the view. Now the problem is if I add more ID
ranges to the partitioning column in the check, sometimes
it worked or sometimes it didn't. See the following sample
code:
-- Create linked server SERVER0,SERVER1 on two SQL
servers.
-- SERVER0 one one machine
exec sp_addlinkedserver 'SERVER0', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC OUT', @.optvalue='TRUE'
-- SERVER1 on another machine
exec sp_addlinkedserver 'SERVER1', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC OUT', @.optvalue='TRUE'
-- Create database Test_DB on each server.
-- ON SERVER1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
[ObjectID] <= 100)
GO
-- ON Server1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 )
GO
-- ON SERVER0: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
GO
--ON SERVER1: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
01-01',1)
It succeeds
-- ON both server, drop the checks
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 )
GO
-- On server1, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 )
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
01-01',1)
It succeeds
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
GO
-- On Server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
600)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
01-01',1)
It succeeds
-- On any server,
Delete vwtest
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
[ObjectID] between 40075 and 50074)
GO
-- On server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
[ObjectID] BETWEEN 50075 and 60074)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
01-01',1)
It will fail. the error message is "UNION ALL view vwtest
is not updatable becuase a partitioning column is not
found."
I am totally lost. Anyone knows how SQL server decides one
column is a partitioning or not. Here I used the same rule
but the result is different.
Any ideas? Thanks in advance.Peter,
did not go through your detailed post. However, I bet that you did your
modification with EM. It is known that when you do such changes in EM to
updateable partitioned view the EM does not do it right. Try use QA. If it
does not work, try recreate the view in QA.
HTH
Quentin
"Peter" <phe@.Visualplant.com> wrote in message
news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> Hi all,
> I am working on a distributed database. I defined linked
> servers, partitioned views etc. I can delete/insert/update
> data from the view. Now the problem is if I add more ID
> ranges to the partitioning column in the check, sometimes
> it worked or sometimes it didn't. See the following sample
> code:
> -- Create linked server SERVER0,SERVER1 on two SQL
> servers.
> -- SERVER0 one one machine
> exec sp_addlinkedserver 'SERVER0', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- SERVER1 on another machine
> exec sp_addlinkedserver 'SERVER1', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- Create database Test_DB on each server.
> -- ON SERVER1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> [ObjectID] <= 100)
> GO
> -- ON Server1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 )
> GO
>
> -- ON SERVER0: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> GO
> --ON SERVER1: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
> 01-01',1)
> It succeeds
> -- ON both server, drop the checks
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 )
> GO
> -- On server1, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 )
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
> 01-01',1)
> It succeeds
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> GO
> -- On Server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
> 600)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
> 01-01',1)
> It succeeds
> -- On any server,
> Delete vwtest
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
> [ObjectID] between 40075 and 50074)
> GO
> -- On server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
> [ObjectID] BETWEEN 50075 and 60074)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
> 01-01',1)
> It will fail. the error message is "UNION ALL view vwtest
> is not updatable becuase a partitioning column is not
> found."
> I am totally lost. Anyone knows how SQL server decides one
> column is a partitioning or not. Here I used the same rule
> but the result is different.
>
> Any ideas? Thanks in advance.
>|||Thanks for your reply.
However, I didn't change the constraint from EM. What I
did is that drop the constraint for all servers, then
create the constraint for all servers from QA. It worked
in some cases. It seems if I have more ID ranges or I have
ID ranges with negative value, it will fail. I tried to
recreate the view, it didn't work too.
The code I posted is exactly what I ran in QA.
>--Original Message--
>Peter,
>did not go through your detailed post. However, I bet
that you did your
>modification with EM. It is known that when you do such
changes in EM to
>updateable partitioned view the EM does not do it right.
Try use QA. If it
>does not work, try recreate the view in QA.
>HTH
>Quentin
>"Peter" <phe@.Visualplant.com> wrote in message
>news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> Hi all,
>> I am working on a distributed database. I defined
linked
>> servers, partitioned views etc. I can
delete/insert/update
>> data from the view. Now the problem is if I add more ID
>> ranges to the partitioning column in the check,
sometimes
>> it worked or sometimes it didn't. See the following
sample
>> code:
>> -- Create linked server SERVER0,SERVER1 on two SQL
>> servers.
>> -- SERVER0 one one machine
>> exec sp_addlinkedserver 'SERVER0', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- SERVER1 on another machine
>> exec sp_addlinkedserver 'SERVER1', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- Create database Test_DB on each server.
>> -- ON SERVER1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> [ObjectID] <= 100)
>> GO
>> -- ON Server1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 )
>> GO
>>
>> -- ON SERVER0: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> --ON SERVER1: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(10,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(110,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server, drop the checks
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 )
>> GO
>> -- On server1, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 )
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(11,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(111,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> GO
>> -- On Server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
>= -
>> 600)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(13,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(113,'2003-
>> 01-01',1)
>> It succeeds
>> -- On any server,
>> Delete vwtest
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
or
>> [ObjectID] between 40075 and 50074)
>> GO
>> -- On server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
OR
>> [ObjectID] BETWEEN 50075 and 60074)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(17,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(117,'2003-
>> 01-01',1)
>> It will fail. the error message is "UNION ALL view
vwtest
>> is not updatable becuase a partitioning column is not
>> found."
>> I am totally lost. Anyone knows how SQL server decides
one
>> column is a partitioning or not. Here I used the same
rule
>> but the result is different.
>>
>> Any ideas? Thanks in advance.
>
>.
>|||Peter,
Oops.
I saw you used Alter Table to add the constraint. Did you try to create the
constraint together with the table creation? Try that.
Quentin
"peter" <phe@.VisualPlant.com> wrote in message
news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
> Thanks for your reply.
> However, I didn't change the constraint from EM. What I
> did is that drop the constraint for all servers, then
> create the constraint for all servers from QA. It worked
> in some cases. It seems if I have more ID ranges or I have
> ID ranges with negative value, it will fail. I tried to
> recreate the view, it didn't work too.
> The code I posted is exactly what I ran in QA.
>
> >--Original Message--
> >Peter,
> >
> >did not go through your detailed post. However, I bet
> that you did your
> >modification with EM. It is known that when you do such
> changes in EM to
> >updateable partitioned view the EM does not do it right.
> Try use QA. If it
> >does not work, try recreate the view in QA.
> >
> >HTH
> >
> >Quentin
> >
> >"Peter" <phe@.Visualplant.com> wrote in message
> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> >> Hi all,
> >>
> >> I am working on a distributed database. I defined
> linked
> >> servers, partitioned views etc. I can
> delete/insert/update
> >> data from the view. Now the problem is if I add more ID
> >> ranges to the partitioning column in the check,
> sometimes
> >> it worked or sometimes it didn't. See the following
> sample
> >> code:
> >>
> >> -- Create linked server SERVER0,SERVER1 on two SQL
> >> servers.
> >> -- SERVER0 one one machine
> >> exec sp_addlinkedserver 'SERVER0', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >> -- SERVER1 on another machine
> >> exec sp_addlinkedserver 'SERVER1', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >>
> >> -- Create database Test_DB on each server.
> >> -- ON SERVER1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> >> [ObjectID] <= 100)
> >> GO
> >>
> >> -- ON Server1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 )
> >> GO
> >>
> >>
> >> -- ON SERVER0: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> --ON SERVER1: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (10,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (110,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server, drop the checks
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 )
> >> GO
> >> -- On server1, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 )
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (11,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (111,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> >> GO
> >> -- On Server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
> >= -
> >> 600)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (13,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (113,'2003-
> >> 01-01',1)
> >> It succeeds
> >>
> >> -- On any server,
> >> Delete vwtest
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
> or
> >> [ObjectID] between 40075 and 50074)
> >> GO
> >> -- On server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
> OR
> >> [ObjectID] BETWEEN 50075 and 60074)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (17,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (117,'2003-
> >> 01-01',1)
> >>
> >> It will fail. the error message is "UNION ALL view
> vwtest
> >> is not updatable becuase a partitioning column is not
> >> found."
> >>
> >> I am totally lost. Anyone knows how SQL server decides
> one
> >> column is a partitioning or not. Here I used the same
> rule
> >> but the result is different.
> >>
> >>
> >> Any ideas? Thanks in advance.
> >>
> >
> >
> >.
> >|||The result is the same. I have other aprtitioned tables
that work well. But the partitioned column of this table
has negative IDs. I think this is the reason.
>--Original Message--
>Peter,
>Oops.
>I saw you used Alter Table to add the constraint. Did
you try to create the
>constraint together with the table creation? Try that.
>Quentin
>
>"peter" <phe@.VisualPlant.com> wrote in message
>news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
>> Thanks for your reply.
>> However, I didn't change the constraint from EM. What I
>> did is that drop the constraint for all servers, then
>> create the constraint for all servers from QA. It worked
>> in some cases. It seems if I have more ID ranges or I
have
>> ID ranges with negative value, it will fail. I tried to
>> recreate the view, it didn't work too.
>> The code I posted is exactly what I ran in QA.
>>
>> >--Original Message--
>> >Peter,
>> >
>> >did not go through your detailed post. However, I bet
>> that you did your
>> >modification with EM. It is known that when you do
such
>> changes in EM to
>> >updateable partitioned view the EM does not do it
right.
>> Try use QA. If it
>> >does not work, try recreate the view in QA.
>> >
>> >HTH
>> >
>> >Quentin
>> >
>> >"Peter" <phe@.Visualplant.com> wrote in message
>> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> >> Hi all,
>> >>
>> >> I am working on a distributed database. I defined
>> linked
>> >> servers, partitioned views etc. I can
>> delete/insert/update
>> >> data from the view. Now the problem is if I add more
ID
>> >> ranges to the partitioning column in the check,
>> sometimes
>> >> it worked or sometimes it didn't. See the following
>> sample
>> >> code:
>> >>
>> >> -- Create linked server SERVER0,SERVER1 on two SQL
>> >> servers.
>> >> -- SERVER0 one one machine
>> >> exec sp_addlinkedserver 'SERVER0', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >> -- SERVER1 on another machine
>> >> exec sp_addlinkedserver 'SERVER1', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >>
>> >> -- Create database Test_DB on each server.
>> >> -- ON SERVER1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> >> [ObjectID] <= 100)
>> >> GO
>> >>
>> >> -- ON Server1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 )
>> >> GO
>> >>
>> >>
>> >> -- ON SERVER0: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> --ON SERVER1: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (10,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (110,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server, drop the checks
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 )
>> >> GO
>> >> -- On server1, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 )
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (11,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (111,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> >> GO
>> >> -- On Server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and
[ObjectID]
>> >= -
>> >> 600)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (13,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (113,'2003-
>> >> 01-01',1)
>> >> It succeeds
>> >>
>> >> -- On any server,
>> >> Delete vwtest
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
>> or
>> >> [ObjectID] between 40075 and 50074)
>> >> GO
>> >> -- On server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and
40074
>> OR
>> >> [ObjectID] BETWEEN 50075 and 60074)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (17,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (117,'2003-
>> >> 01-01',1)
>> >>
>> >> It will fail. the error message is "UNION ALL view
>> vwtest
>> >> is not updatable becuase a partitioning column is not
>> >> found."
>> >>
>> >> I am totally lost. Anyone knows how SQL server
decides
>> one
>> >> column is a partitioning or not. Here I used the same
>> rule
>> >> but the result is different.
>> >>
>> >>
>> >> Any ideas? Thanks in advance.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I have the same problem. My view is local and I can
insert using table names, but only read using view.