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*
FROMServer1..pTable1
UNION ALL
SELECT*
FROMServer2..pTable2

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

...<other columns>.......

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 ,

...<other columns>.......

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.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" <SonnyKMI@.gmail.comwrote in message
news:1180643932.644398.247270@.g37g2000prf.googlegr oups.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 ,

...<other columns>.......

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 ,

...<other columns>.......

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.|||On May 31, 4:17 pm, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

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.|||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" <SonnyKMI@.gmail.comwrote in message
news:1180647134.671664.320360@.a26g2000pre.googlegr oups.com...
On May 31, 4:17 pm, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

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.|||Sonny (SonnyKMI@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sonny (SonnyKMI@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

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/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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.|||Check out:

http://msdn2.microsoft.com/en-us/li...18(SQL.80).aspx
--
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" <SonnyKMI@.gmail.comwrote in message
news:1180703291.252760.209290@.a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

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/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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.|||On Jun 1, 8:12 am, "Tom Moreau" <t...@.dont.spam.me.cips.cawrote:

Quote:

Originally Posted by

Check out:
>
http://msdn2.microsoft.com/en-us/li...18(SQL.80).aspx
>
--
Tom
>
----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canadahttps://mvp.support.microsoft.com/profile/Tom.Moreau
>
"Sonny" <Sonny...@.gmail.comwrote in message
>
news:1180703291.252760.209290@.a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
>

Quote:

Originally Posted by

Sonny (Sonny...@.gmail.com) writes:

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Books Online for SQL Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.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!!sql

Help on calculated member

I don't know how to do this.

I have one table "services" with a field "clasification" and a another table "serviceschange" with a field "clasificationchange". The two tables are related with foreign key. The serviceschange table has a field idservices (a fk to the services table).

I want to show in the fact table the name and the clasification of a service (the name is in the services table) but for the clasification i have to do this:

If the id of the service is in the serviceschange table i have to show the clasifiactionchange of this table, but if the serviceschange doesn't have the id i want to show the field clasification of the services table. How i can do this? is it possibly?

Assuming AS 2005, and that services is a dimension, classification can be defined as an attribute of the services dimension. A named query like the following could be created in the DSV, to be used as the dimension table for services (if there is at most 1 entry for a service in serviceschange - otherwise grouping may be necessary):

>>

select sv.idservices, sv.servicename, coalesce(sc.classificationchange, sv.classification) as classification

from services sv

left outer join serviceschange sc

on sv.idservices = sc.idservices

>>

sql

help me in sql server codes

Hi,

I am new member here.
I have post system , and the items sends from sender to receiver.
The item takes from 24 hors to 168 hours to reach to the receiver.
I use MS SQL SERVER 2000, I have database (EMS_IN_REPORT)
And seven fields.
The field EVENT_TYPE has many letters, for example: A, C, D, H AND I.
I want make query include the following:
1- every field (ITEM_IDENTIFICATION) must has letters c and d in the
(EVENT_TYPE) field.
2-the total number of items.
3-make percentage for every type:24,48,72,96,120,144,168
4-make the total number for every type.

As the following picture:

and the final result must entry in this table:

Please help me and thank you for all.
Regards,
th goldwhy no any reply?!!!

please help me .|||please any one can help me????