Problems with partition view because it is evaluation version?

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
GlynTry specifying the check constraint as part of the CREATE TABLE. For
example:
CREATE TABLE [dbo].[bb_chat_200309] (
[chatID] [numeric](18, 0) IDENTITY (1, 1) NOT
NULL ,
[yearMonth] [varchar] (6) NOT NULL
CONSTRAINT CK_bb_chat_200309_yearmonth CHECK ([yearMonth] = '200309') ,
[originatedBy] [varchar] (12) NULL ,
[originatedDate] [datetime] NULL ,
[terminatedBy] [varchar] (12) NULL ,
[terminatedDate] [datetime] NULL
) ON [PRIMARY]
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Glyn Walters" <glyn_walters@.hotmail.com> wrote in message
news:f9b601c43e65$9cba4a90$a601280a@.phx.gbl...
> 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|||Hi. Thanks for the advice. Howvere I do create the tables
like that. The output above was from exporting sql. I
create the tables using this script:
set @.sql = 'create table dbo.bb_chatUser_' + @.YYYYMM + '(
chatID numeric(18, 0) not null
, yearMonth varchar(6) not null constraint
ck_bb_chatUser_' + @.YYYYMM + '_yearmonth check (yearMonth
= ' + @.YYYYMM + ')
, invitedBy varchar (12) not null
, invitedDate datetime not null
, guestID varchar(12) not null
, answeredDate datetime null
, leftDate datetime
)'
exec(@.sql);
I think my tables and view qualify as yearMonth has a non-
overlapping check constraint and is part of the primary
key. Are there any other things I need to do to make the
view updateable?|||drop and re-create the constraints, there is an article by MS
http://support.microsoft.com/default.aspx?scid=kb;en-us;270013|||Thanks for the advice. If was my mistake in the end the
check constraints were yearMonth = 200310 rather
than '200310'.sql