Partition view problem - is it because I am using evaluation version? 1

[gdub]

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

 

[vongrunt]

One suspect: partitioned views don't like identities during INSERT statements (chatID).

 

[vongrunt]

... and yes, only enterprise and developer 2k editions allow updateable partitioned views.

 

[gdub]

Thanks. You are right. The identities have to go. But the first problem was my check constraints were yearMonth = 200310 rather than '200310'.

So it looks like evaluation version has capabilities of enterprise version.