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. 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.
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.
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.
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
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.
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.
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!! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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)....
|
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),...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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'
...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
| |