November 1, 2002 at 5:17 pm
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
November 2, 2002 at 6:43 am
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
November 2, 2002 at 10:57 am
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" ?
November 3, 2002 at 3:58 am
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
November 4, 2002 at 11:18 am
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
November 4, 2002 at 9:05 pm
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.
November 5, 2002 at 10:39 am
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
November 5, 2002 at 1:11 pm
Do your tables have any triggers or update/delete cascading?
Matthew Burr
November 5, 2002 at 2:23 pm
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
November 5, 2002 at 2:37 pm
There are no such Data Modification Rules lised in BOL, Could it be a bug?
November 5, 2002 at 2:46 pm
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply