473,322 Members | 1,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 ,

...<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.

May 31 '07 #1
8 11242
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" <So******@gmail.comwrote in message
news:11**********************@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.

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

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

May 31 '07 #4
Sonny (So******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 31 '07 #5
Sonny (So******@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 31 '07 #6
On May 31, 4:58 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
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/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.

Jun 1 '07 #7
Check out:

http://msdn2.microsoft.com/en-us/lib...8(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" <So******@gmail.comwrote in message
news:11**********************@a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
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/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.

Jun 1 '07 #8
On Jun 1, 8:12 am, "Tom Moreau" <t...@dont.spam.me.cips.cawrote:
Check out:

http://msdn2.microsoft.com/en-us/lib...8(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:11**********************@a26g2000pre.googlegr oups.com...
On May 31, 4:58 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
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/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!!

Jun 1 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: Jeff Boes | last post by:
I'm sure this is a concept that's been explored here. I have a table (fairly simple, just two columns, one of which is a 32-digit checksum) with several million rows (currently, about 7 million)....
7
by: Jane | last post by:
In Oracle we can partition a table as follows. What is the equivalent in DB2? CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_state VARCHAR2(20),...
7
by: Rajesh.............................. | last post by:
What is the impact of using a nullable column vs a not nullable column for partitioning a Union ALL View? I have a Union ALL View with ten underlying tables unioned based on different values for a...
2
by: db2admin | last post by:
hi, does cost of SQL include broadcasting etc. as additional cost ? cost of SQL doing join on partitioning key is less than cost of same SQL doing join on cluster index which has column other than...
0
by: Robert Klemme | last post by:
Hi, this is just to verify: I checked the documentation and it seems that SQL 2005 does not support hash partitioning on a number of columns. As far as I can see only range partitioning is...
10
by: shsandeep | last post by:
DB2 V8.2 (not Viper yet and no range partitioning!!) I have created a table T1 (col1, col2) with col1 as the primary key. When I try to create a partitioning key on col2, it gives me error that it...
8
by: mitek | last post by:
Hi, All I have strange situation with table design for DB2 9.1 on Windows I have 3 tables with same structure : 1 table - is MDC 2 table - is partitioned MDC table 3 table - is compressed...
1
by: Ivory056mt | last post by:
In SQL 2000, can you use a GUID as a partitioning column? The query select * from table where b_id between '00000000-0000-0000-0000-600000000000' and 'FFFFFFFF-FFFF-FFFF-FFFF-6FFFFFFFFFFF' ...
15
by: Piero 'Giops' Giorgi | last post by:
Hi! I have a question: I already have a DB that uses partitions to divide data in US Counties, partitioned by state. Can I use TWO levels of partitioning? I mean... 3077 filegroups and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.