Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Can you fix the bug in "Partitioned VIEWS" ?

-- A partitioned view joins data that is horizontally split.

/* Create an employees table for employees 100 - 199 */

USE Sample
GO

CREATE TABLE dbo.Employees_1
(
    EmpID [tinyint] IDENTITY (100, 1) PRIMARY KEY CHECK (EmpID BETWEEN 100 AND 199),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)
GO

INSERT INTO Employees_1 VALUES ('Peddi', 'Kishore', '01/01/2006')
GO

INSERT INTO Employees_1 VALUES ('Ponnaganti', 'Lohiya', '02/02/2006')
GO

INSERT INTO Employees_1 VALUES ('Peddi', 'Suresh', '03/03/2006')
GO

INSERT INTO Employees_1 VALUES ('Kommineni', 'Swapna', '04/04/2006')
GO

/* Create an employees table for employees 200 - 299 */

USE Sample
GO

CREATE TABLE dbo.Employees_2
(
    EmpID [tinyint] IDENTITY (200, 1) PRIMARY KEY CHECK (EmpID BETWEEN 200 AND 299),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)
GO

INSERT INTO Employees_2 VALUES ('Mokkapati', 'Sreenivas', '01/01/2006')
GO

INSERT INTO Employees_2 VALUES ('Dhanekula', 'Tagore', '02/02/2006')
GO

INSERT INTO Employees_2 VALUES ('Singam', 'Ramesh', '03/03/2006')
GO

INSERT INTO Employees_2 VALUES ('Buddi', 'Ravi', '04/04/2006')
GO

/* Create the view on same server */

USE Sample
GO

CREATE VIEW Employees_View

AS

SELECT * FROM dbo.Employees_1

UNION ALL

SELECT * FROM dbo.Employees_2

GO

-- Run the view

SELECT * FROM Employees_View

GO

-- Insert / Update / Delete => view    //ERROR

UPDATE Employees_View

SET   LastName = 'Suman',
      FirstName = 'Chundi',
      BirthDate = '01/01/2006'

WHERE EmpID = 101

GO

-- Run the view

SELECT * FROM Employees_View

GO

When i have done the following command...i am getting one error:
==========================================

-- Insert / Update / Delete => view

UPDATE Employees_View

SET   LastName = 'Suman',
      FirstName = 'Chundi',
      BirthDate = '01/01/2006'

WHERE EmpID = 101

GO

ERROR:
=====

UNION ALL view 'Employees_View' is not updatable because a partitioning column was not found.

Can you modify the above code so that it will work fine !!
0
kishore_peddi
Asked:
kishore_peddi
  • 3
  • 3
  • 2
1 Solution
 
appariCommented:
i think you cannot use identity column as partitioned column,
try changing the tables definition as follows

CREATE TABLE dbo.Employees_1
(
    EmpID [tinyint] not null PRIMARY KEY CHECK (EmpID BETWEEN 100 AND 199),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)

and
CREATE TABLE dbo.Employees_2
(
    EmpID [tinyint] not null PRIMARY KEY CHECK (EmpID BETWEEN 200 AND 299),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)


then create the view and test again
0
 
appariCommented:

Fromthe following link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_30hj.asp

Conditions for Creating Partitioned Views

SELECT list
All columns in the member tables should be selected in the column list of the view definition.


The columns in the same ordinal position of each select_list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.
Also, at least one column (for example <col>) must appear in all the SELECT lists in the same ordinal position. This <col> should be defined such that the member tables T1, ..., Tn have CHECK constraints C1, ..., Cn defined on <col> respectively.

Constraint C1 defined on table T1 must follow this form:

C1 ::= < simple_interval > [ OR < simple_interval > OR ...]
< simple_interval > :: =
    < col > { < | > | <= | >= | = }
    | < col > BETWEEN < value1 > AND < value2 >
    | < col > IN ( value_list )
    | < col > { > | >= } < value1 > AND
        < col > { < | <= } < value2 >

The constraints should be such that any given value of <col> can satisfy at most one of the constraints C1, ..., Cn so that the constraints should form a set of disjointed or non-overlapping intervals. The column <col> on which the disjointed constraints are defined is called the 'partitioning column.' Note that the partitioning column may have different names in the underlying tables. The constraints should be in an enabled state in order for them to meet the above conditions of the partitioning column. If the constraints are disabled, re-enable constraint checking with either the WITH CHECK option or the CHECK constraint_name options of ALTER TABLE.
Here are some examples of valid sets of constraints:

{ [col < 10], [col between 11 and 20] , [col > 20] }
{ [col between 11 and 20], [col between 21 and 30], [col between 31 and 100] }

The same column cannot be used multiple times in the SELECT list.
Partitioning column
The partitioning column is a part of the PRIMARY KEY of the table.


It cannot be a computed, identity, default, or timestamp column.


If there is more than one constraint on the same column in a member table, SQL Server ignores all the constraints and will not consider them when determining whether or not the view is a partitioned view. To meet the conditions of the partitioned view, there should be only one partitioning constraint on the partitioning column.


There are no restrictions on the updatability of the partitioning column.
0
 
kishore_peddiAuthor Commented:

-- A partitioned view joins data that is horizontally split.

-- A local partitioned view has all underlying tables in the same instance of SQL Server.  

/* Create an employees table for employees 100 - 199 */

USE Sample
GO

CREATE TABLE dbo.Employees_1
(
    EmpID [tinyint] NOT NULL PRIMARY KEY CHECK (EmpID BETWEEN 100 AND 199),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)
GO

INSERT INTO Employees_1 VALUES (101, 'Peddi', 'Kishore', '01/01/2006')
GO

INSERT INTO Employees_1 VALUES (102, 'Ponnaganti', 'Lohiya', '02/02/2006')
GO

INSERT INTO Employees_1 VALUES (103, 'Peddi', 'Suresh', '03/03/2006')
GO

INSERT INTO Employees_1 VALUES (104, 'Kommineni', 'Swapna', '04/04/2006')
GO

/* Create an employees table for employees 200 - 299 */

USE Sample
GO

CREATE TABLE dbo.Employees_2
(
    EmpID [tinyint] NOT NULL PRIMARY KEY CHECK (EmpID BETWEEN 200 AND 299),
    LastName nvarchar(20) NOT NULL ,
    FirstName nvarchar(10) NOT NULL ,
    BirthDate smalldatetime NULL
)
GO

INSERT INTO Employees_2 VALUES (200, 'Mokkapati', 'Sreenivas', '01/01/2006')
GO

INSERT INTO Employees_2 VALUES (201, 'Dhanekula', 'Tagore', '02/02/2006')
GO

INSERT INTO Employees_2 VALUES (202, 'Singam', 'Ramesh', '03/03/2006')
GO

INSERT INTO Employees_2 VALUES (203, 'Buddi', 'Ravi', '04/04/2006')
GO

/* Create the view on same server */

USE Sample
GO

CREATE VIEW dbo.Employees_View

AS

SELECT * FROM dbo.Employees_1

UNION ALL

SELECT * FROM dbo.Employees_2

GO

-- Run the view

SELECT * FROM Employees_View
GO

-- Insert / Update / Delete => view

UPDATE Employees_View

SET   LastName = 'Suman',
      FirstName = 'Chundi'

WHERE EmpID = 101

GO

-- Run the view

SELECT * FROM Employees_View
GO



NOTE:
====

Still got the same error !! Can you please check ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
imran_fastCommented:
hi kishore_peddi ,

Your code is fine there is nothing wrongn in it but check the version of sql you have.

select @@version  

Note: You can modify data through a partitioned view only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft. SQL Server 2000 Developer Edition.

Regards

Imran

0
 
kishore_peddiAuthor Commented:
This is what i am having on my machine...


Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
      Dec 17 2002 14:22:05
      Copyright (c) 1988-2003 Microsoft Corporation
      Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Thanks,
Kishore
0
 
imran_fastCommented:
hi kishore_peddi,
do you have latest service pack 4 for sql 2000.
0
 
kishore_peddiAuthor Commented:
Do we need service pack 4 for sql 2000 ?
0
 
imran_fastCommented:
Its always better to have latest service pack installed.
You can download it from microsoft site for free.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now