Hi
I am trying to get a partition view to work. The partition view is made up
of tables that as far as I can tell qualify for having a partion column.
Each tables is identical except for the check constraint.
If I try to update a row I get an error that partioning column was not
found.
Updateable partition views only work on the enterprise edition as I
understand it. I am trying this out on the evaluation edition. Could this be
the problem or is it more likely to be in my view construction?
Sql is:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bb_chat_200310] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[bb_chat_200309] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200309] PRIMARY KEY CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200309)
GO
ALTER TABLE [dbo].[bb_chat_200310] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200310] PRIMARY KEY CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200310)
GO
create view dbo.bb_chat as
select
chatID
, yearMonth
, originatedBy
, originatedDate
, terminatedBy
, terminatedDate
from bb_chat_200405
union all
select
chatID
, yearMonth
, originatedBy
, originatedDate
, terminatedBy
, terminatedDate
from bb_chat_200404
Thanks for any help
Glyn
I am trying to get a partition view to work. The partition view is made up
of tables that as far as I can tell qualify for having a partion column.
Each tables is identical except for the check constraint.
If I try to update a row I get an error that partioning column was not
found.
Updateable partition views only work on the enterprise edition as I
understand it. I am trying this out on the evaluation edition. Could this be
the problem or is it more likely to be in my view construction?
Sql is:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[bb_chat_200310] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[yearMonth] [varchar] (6) NOT NULL ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[bb_chat_200309] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200309] PRIMARY KEY CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200309)
GO
ALTER TABLE [dbo].[bb_chat_200310] WITH NOCHECK ADD
CONSTRAINT [pk_bb_chat_200310] PRIMARY KEY CLUSTERED
(
[chatID],
[yearMonth]
) ON [PRIMARY] ,
CHECK ([yearMonth] = 200310)
GO
create view dbo.bb_chat as
select
chatID
, yearMonth
, originatedBy
, originatedDate
, terminatedBy
, terminatedDate
from bb_chat_200405
union all
select
chatID
, yearMonth
, originatedBy
, originatedDate
, terminatedBy
, terminatedDate
from bb_chat_200404
Thanks for any help
Glyn