Distributed Partitioned Views -

Question

Distributed Partitioned Views -

tejeshc

Old Hand

Points: 307
More actions
November 1, 2002 at 5:17 pm

#105912

Hi all ,
I am in process of setting up SQL 2K cluster and looking at using distributed partitioned views.
I set up the base tables across 2 different instances of SQL 2k using linked servers.
we have multicolumn Primary Key constraint on the table and the partitioned column is by Districts (part of the PK ). The view is defined as UNION ALL of the Select statement from the 2 different linked servers.
My problem is that the view works fine when I use SELECT for read purposes but when I try to UPDATE the view it gives this error.
Server: Msg 4416, Level 16, State 5, Line 1
UNION ALL view 'ttodetail' is not updatable because the definition contains a disallowed construct.
I looked at BOL and other areas - I am following most of the required conditions for updateable partitioned views.
I would appreciate if anyone has any suggestions on this.
Thanks in Advance

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 1

How have you created your tables and union. You need check constraints on the tables to partition the data. i.e on has districts 1 to 10 and on has all the other districts.

You need to cover 100% of the cases, i.e you can't just cover district 1 and 2

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

tejeshc Old Hand Points: 307 More actions · Answer 2

Thanks for the reply.

yes I did cover all the cases. I dropped and recreated 2 tables and used check constraint on the district column.

Then I populated the tables with the values following the check constraint definition.

It still gives the same error. I am wondering what is the "disallowed construct" ?

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

What is the definition of your tables and view?

Checkout everything in here,

http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_06_17zr.asp

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

tejeshc Old Hand Points: 307 More actions · Answer 4

Here is the definition of my table. One has check constraint for district = '01' and another for district = '02'

The view definition is as follows:

create view ttodetail

AS

SELECT * FROM ttodetail_1

UNION ALL

SELECT * FROM [GALAXYSC2\S].Galaxy.dbo.ttodetail_1

I removed all Default constraints on the columns also. It seems there is something in here which is disallowed construct.

If u have any suggestions on this. pls let me know.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttodetail_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ttodetail_1]

GO

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttodetail_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN

CREATE TABLE [dbo].[ttodetail_1] (

[fy_yy] [char] (4) NOT NULL ,

[semstr_i] [char] (1) NOT NULL ,

[fiscal_dst] [char] (2) NOT NULL ,

[oru_i] [char] (6) NOT NULL ,

[to_mod_no] [int] NOT NULL ,

[to_item_no] [int] NOT NULL ,

[to_item_seq_no] [int] NOT NULL ,

[draft_no] [int] NOT NULL ,

[version_no] [int] NOT NULL ,

[mod_st] [int] NULL ,

[bdgt_amt] [decimal](12, 4) NULL ,

[prev_bdgt_amt] [decimal](12, 4) NULL ,

[bdgt_diff_amt] [decimal](12, 4) NULL ,

[bdgt_fte] [decimal](12, 4) NULL ,

[funct_c] [int] NULL ,

[org_cat] [int] NULL ,

[assign_c] [int] NULL ,

[assign_fte] [decimal](12, 4) NULL ,

[max_assign_fte] [decimal](12, 4) NULL ,

[assign_eff_d] [smalldatetime] NULL ,

[assign_end_d] [smalldatetime] NULL ,

[pr_i] [char] (7) NULL ,

[pr_name] [char] (25) NULL ,

[ssn] [char] (9) NULL ,

[ttl_i] [char] (5) NULL ,

[subj_c] [int] NULL ,

[class_cohort_c] [char] (15) NULL ,

[mode_c] [int] NULL ,

[loi_c] [int] NULL ,

[pop_typ] [int] NULL ,

[grade_typ] [varchar] (100) NULL ,

[pgm] [int] NULL ,

[pgm_desc] [char] (72) NULL ,

[initiative] [char] (25) NULL ,

[hh] [char] (1) NOT NULL ,

[hh_rsn_c] [int] NULL ,

[mod_trg_f] [char] (1) NULL ,

[hyb_itn_ind] [char] (1) NULL ,

[lock_row_f] [decimal](12, 4) NULL ,

[int_pri_i] [char] (7) NULL ,

[int_prsn_n] [char] (25) NULL ,

[int_ssn] [char] (9) NULL ,

[stf_sta] [char] (3) NULL ,

[j_i] [char] (5) NULL ,

[dcl_j_i] [char] (1) NULL ,

[expenditure_i] [int] NULL ,

[cls_desig] [char] (25) NULL ,

[create_ts] [datetime] NULL ,

[create_user_i] [char] (8) NULL ,

[lst_upd_ts] [datetime] NULL ,

[lst_upd_user_i] [char] (8) NULL ,

[blank_req_fld] [char] (1) NOT NULL ,

[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,

[AUTOMOD] [char] (1) NULL ,

[payroll_qc_adj] [char] (1) NOT NULL ,

[academy_id] [smallint] NULL ,

[room] [varchar] (10) NULL ,

[to_item_key] [int] NULL ,

[trans_typ] [int] NULL ,

[grade_desc] [varchar] (400) NULL ,

[prev_funct_c] [int] NULL ,

[split_key] [int] NULL ,

CONSTRAINT [PK_ttodetail_1] PRIMARY KEY CLUSTERED

(

[fy_yy],

[semstr_i],

[fiscal_dst],

[oru_i],

[to_mod_no],

[to_item_no],

[to_item_seq_no],

[draft_no],

[version_no]

),

CHECK ([fiscal_dst] = '01' )

)

END

GO

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 5

What you should do is create An instead Of trigger for update, so when you try to update the view, depending of what records you try to update, the trigger updates the corresponding table of the partitioned view.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 6

What is the update statement you are using?

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

mdburr Default port Points: 1483 More actions · Answer 7

Do your tables have any triggers or update/delete cascading?

Matthew Burr

tejeshc Old Hand Points: 307 More actions · Answer 8

Hi all ,

I found the "disallowed construct". I had a few columns defined as decimal ( 12, 4) and smalldatetime. When I changed the decimal to numeric and smalldatetime to datetime , it works fine now. I just went thru a painful elimination process and came upon the solution. Updates, deletes and Inserts are all working fine now.

I still need to do further testing from my application though.

Thanks for all your help

Cheers

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 9

There are no such Data Modification Rules lised in BOL, Could it be a bug?

tejeshc Old Hand Points: 307 More actions · Answer 10

yes It does look like a bug. I am running SQL2k with SP2. I found it really strange that it does not allow these datatypes. I am going to do some more experimentation on this.