Help on Partitioning column was not found.

Hi,

I don't know if I missed anything. I have 2 member tables and one
partition view in SQL 2000 defined as following

CREATE VIEW Server1.dbo.UTable
AS
SELECT *
FROM Server1..pTable1
UNION ALL
SELECT *
FROM Server2..pTable2

CREATE TABLE pTable1 (
[ID1] [int] IDENTITY (1000, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

............

CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED
(
[ID1],
[ID2]
) ON [PRIMARY] ,
CHECK ([ID2] = 1015)
) ON [PRIMARY]


CREATE TABLE [pTable2] (
[ID1] [int] IDENTITY (1001, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

............

CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED
(
[ID1],
[ID2]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CHECK ([ID2] <> 1015)
) ON [PRIMARY]


SELECT is working fine. However, I got error message if I issue an
update command such as

UPDATE UTable
SET somecol = someval
Where somecol2 = somecond

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'UTable' is not updatable because a partitioning column
was not found.

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Thanks a lot.

Re: Help on Partitioning column was not found.

You cannot have identity columns in an updatable partitioned view.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Sonny" wrote in message
news:1180643932.644398.247270@g37g2000prf.googlegroups.com.. .
Hi,

I don't know if I missed anything. I have 2 member tables and one
partition view in SQL 2000 defined as following

CREATE VIEW Server1.dbo.UTable
AS
SELECT *
FROM Server1..pTable1
UNION ALL
SELECT *
FROM Server2..pTable2

CREATE TABLE pTable1 (
[ID1] [int] IDENTITY (1000, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

.............

CONSTRAINT [PK_tblLot] PRIMARY KEY CLUSTERED
(
[ID1],
[ID2]
) ON [PRIMARY] ,
CHECK ([ID2] = 1015)
) ON [PRIMARY]


CREATE TABLE [pTable2] (
[ID1] [int] IDENTITY (1001, 2) NOT NULL ,
[ID2] [int] NOT NULL ,

.............

CONSTRAINT [PK_tblLot] PRIMARY KEY NONCLUSTERED
(
[ID1],
[ID2]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CHECK ([ID2] <> 1015)
) ON [PRIMARY]


SELECT is working fine. However, I got error message if I issue an
update command such as

UPDATE UTable
SET somecol = someval
Where somecol2 = somecond

Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'UTable' is not updatable because a partitioning column
was not found.

Anyone have any idea? ID2 is my partition column, why the SQL 2K
doesn't see it. It is a part of primary key, having checking
constrain, and no other constrain on it. Am I missing something?

Thanks a lot.

Re: Help on Partitioning column was not found.

On May 31, 4:17 pm, "Tom Moreau" wrote:
> You cannot have identity columns in an updatable partitioned view.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

Re: Help on Partitioning column was not found.

Consider putting an INSTEAD OF trigger on the partitioned view.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Sonny" wrote in message
news:1180647134.671664.320360@a26g2000pre.googlegroups.com.. .
On May 31, 4:17 pm, "Tom Moreau" wrote:
> You cannot have identity columns in an updatable partitioned view.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>

In that case, how should I deal with the ID1? I need that column to
be an identity column. Thanks.

Re: Help on Partitioning column was not found.

Sonny (SonnyKMI@gmail.com) writes:
> Anyone have any idea? ID2 is my partition column, why the SQL 2K
> doesn't see it. It is a part of primary key, having checking
> constrain, and no other constrain on it. Am I missing something?

Yes, <> is not a permitted operator. You need to rewrite

CHECK ([ID2] <> 1015)

to

CHECK ([ID2] < 1015 OR [ID2] > 1015)

Another story is whether this view will be very efficient. You should
probably add an index on ID2, or put it first in the primary key.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Help on Partitioning column was not found.

Sonny (SonnyKMI@gmail.com) writes:
> In that case, how should I deal with the ID1? I need that column to
> be an identity column. Thanks.

Oh, I should have added the the IDENTITY appears to work fine, as soon
as I had changed the CHECK constraint.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Help on Partitioning column was not found.

On May 31, 4:58 pm, Erland Sommarskog wrote:
> Sonny (Sonny...@gmail.com) writes:
> > In that case, how should I deal with the ID1? I need that column to
> > be an identity column. Thanks.
>
> Oh, I should have added the the IDENTITY appears to work fine, as soon
> as I had changed the CHECK constraint.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.

Again, thank you very much for your help.

Re: Help on Partitioning column was not found.

Check out:

http://msdn2.microsoft.com/en-us/library/aa224818(SQL.80).as px

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Sonny" wrote in message
news:1180703291.252760.209290@a26g2000pre.googlegroups.com.. .
On May 31, 4:58 pm, Erland Sommarskog wrote:
> Sonny (Sonny...@gmail.com) writes:
> > In that case, how should I deal with the ID1? I need that column to
> > be an identity column. Thanks.
>
> Oh, I should have added the the IDENTITY appears to work fine, as soon
> as I had changed the CHECK constraint.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005
> athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000
> athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Thanks for all your help. I changed CHECK constraint, and now it is
not complaining about missing partition column anymore, however, when
do the Update or Insert it gives out Server: Msg 4450, Level 16, State
1, Line 1
Cannot update partitioned view 'UTable' because the definition of the
view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
I think IDENTITY is the another issue. As Tom mentioned in his post,
using INSTEAD OF trigger, would anyone please give me an example,
never used before.

Again, thank you very much for your help.

Re: Help on Partitioning column was not found.

On Jun 1, 8:12 am, "Tom Moreau" wrote:
> Check out:
>
> http://msdn2.microsoft.com/en-us/library/aa224818(SQL.80).as px
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Sonny" wrote in message
>
> news:1180703291.252760.209290@a26g2000pre.googlegroups.com.. .
> On May 31, 4:58 pm, Erland Sommarskog wrote:
>
> > Sonny (Sonny...@gmail.com) writes:
> > > In that case, how should I deal with the ID1? I need that column to
> > > be an identity column. Thanks.
>
> > Oh, I should have added the the IDENTITY appears to work fine, as soon
> > as I had changed the CHECK constraint.
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> > Books Online for SQL Server 2005
> > athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> > Books Online for SQL Server 2000
> > athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
>
> Thanks for all your help. I changed CHECK constraint, and now it is
> not complaining about missing partition column anymore, however, when
> do the Update or Insert it gives out Server: Msg 4450, Level 16, State
> 1, Line 1
> Cannot update partitioned view 'UTable' because the definition of the
> view column 'ID1' in table '[pTable1]' has a IDENTITY constraint. So
> I think IDENTITY is the another issue. As Tom mentioned in his post,
> using INSTEAD OF trigger, would anyone please give me an example,
> never used before.
>
> Again, thank you very much for your help.

Thank you so much!!