by I don't see why this view defined on the following tables is not updatable.
Can anyone help? When I try & do an insert into the view I get the
following error:
Server: Msg 4445, Level 16, State 11, Line 1
UNION ALL view 'CV3OrderDV' is not updatable because the primary key of
table '[MLVW2KTR].[test70].[dbo].[CV3Order]' is not unioned with primary
keys of preceding tables.
I'm using Win 2000 Advanced server and SQL 2000 beta 2.
thanks.
here's the view:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CV3OrderDV]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[CV3OrderDV]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create view CV3OrderDV as
select * from test70.dbo.CV3Order
union all
select * from MLVW2KTR.test70.dbo.CV3Order
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
here's table 1
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CV3Order]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CV3Order]
GO
CREATE TABLE [dbo].[CV3Order] (
[SiteID] [smallint] NULL ,
[RepFlags] [tinyint] NULL ,
[Build] [int] NOT NULL ,
[TouchedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TouchedWhen] [datetime] NULL ,
[CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedWhen] [datetime] NULL ,
[Active] [bit] NOT NULL ,
[GUID] [HVCIDdt] NOT NULL ,
[Entered] [datetime] NULL ,
[Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ToBeVerified] [bit] NOT NULL ,
[ToBeSigned] [bit] NOT NULL ,
[EnterRole] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserGUID] [HVCIDdt] NULL ,
[ClientVisitGUID] [numeric](16, 0) NOT NULL ,
[ClientGUID] [HVCIDdt] NULL ,
[ChartGUID] [HVCIDdt] NULL ,
[Name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Modifier] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequestedDtm] [datetime] NULL ,
[RequestedDate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequestedTime] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StopDtm] [datetime] NULL ,
[StopDate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StopTime] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Duration] [int] NOT NULL ,
[IsDurationChangeable] [bit] NOT NULL ,
[PerformedDtm] [datetime] NULL ,
[SignificantDtm] [datetime] NULL ,
[SignificantTime] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ScheduledDtm] [datetime] NULL ,
[AncillaryReferenceCode] [varchar] (75) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[SummaryLine] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDCode] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApplicSource] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsForDischarge] [bit] NOT NULL ,
[HasBeenModified] [bit] NOT NULL ,
[IsConditional] [bit] NOT NULL ,
[ConditionsText] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IsHeld] [bit] NOT NULL ,
[HoldReasonText] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IsAutoActivatable] [bit] NOT NULL ,
[ActivateDateReference] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ActivateDaysBefore] [int] NOT NULL ,
[ActivateHoursBefore] [int] NOT NULL ,
[ActivateStatus] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumStatusForActivation] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IsAutoCompletable] [bit] NOT NULL ,
[CompleteDateReference] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CompleteDaysAfter] [int] NOT NULL ,
[CompleteHoursAfter] [int] NOT NULL ,
[CompleteStatus] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumStatusForCompletion] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IsCompleteTemplate] [bit] NOT NULL ,
[TranspMethodCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[FrequencyCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderPriorityCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[SystemOrderPriorityCode] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatusCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatusLevelNum] [int] NOT NULL ,
[IsPRN] [bit] NOT NULL ,
[IsSuspended] [bit] NOT NULL ,
[IsPartOfSet] [bit] NOT NULL ,
[SequenceNum] [int] NOT NULL ,
[OrderSetName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderSetHeading] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[OrderSetGUID] [HVCIDdt] NULL ,
[CareProviderGUID] [HVCIDdt] NULL ,
[OrderEntryFormGUID] [HVCIDdt] NULL ,
[OrderCatalogMasterItemGUID] [HVCIDdt] NULL ,
[IsIncluded] [bit] NOT NULL ,
[PathwayColumnHeaderGUID] [HVCIDdt] NULL ,
[PathwayRowHeaderGUID] [HVCIDdt] NULL ,
[OrderSetType] [int] NOT NULL ,
[IsTrackVariance] [bit] NOT NULL ,
[IsGenericSet] [bit] NOT NULL ,
[SubSequenceNum] [int] NOT NULL ,
[ModifyUserGUID] [HVCIDdt] NULL ,
[ArrivalDtm] [datetime] NULL ,
[ModifiedDtm] [datetime] NULL ,
[ReqCodedTime] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReqTimeValue] [int] NULL ,
[ReqTimeUom] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReqTimeEventModifier] [int] NULL ,
[ReqTimeEventCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[VarianceType] [int] NULL ,
[RepeatOrder] [int] NULL ,
[IsAutoGenerated] [tinyint] NULL ,
[OrderTemplateGUID] [HVCIDdt] NULL ,
[IsApplyProtection] [tinyint] NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [OrderClustIdx] ON [dbo].[CV3Order]([ClientGUID],
[ChartGUID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CV3Order] WITH NOCHECK ADD
CONSTRAINT [DF__CV3Order__SiteID__125EB334] DEFAULT (0) FOR [SiteID],
CONSTRAINT [DF__CV3Order__RepFla__1352D76D] DEFAULT (0) FOR [RepFlags],
CONSTRAINT [DF__CV3Order__Touche__1446FBA6] DEFAULT (suser_name(null)) FOR
[TouchedBy],
CONSTRAINT [DF__CV3Order__Touche__153B1FDF] DEFAULT (getdate()) FOR
[TouchedWhen],
CONSTRAINT [DF__CV3Order__Create__162F4418] DEFAULT (suser_name(null)) FOR
[CreatedBy],
CONSTRAINT [DF__CV3Order__Create__17236851] DEFAULT (getdate()) FOR
[CreatedWhen],
CONSTRAINT [CV3OrderPK] PRIMARY KEY NONCLUSTERED
(
[ClientVisitGUID]
) ON [PRIMARY] ,
CONSTRAINT [CK_CV3Order] CHECK ([clientvisitguid] > 5000000000000000)
GO
CREATE INDEX [OrderAlt1Idx] ON [dbo].[CV3Order]([IDCode]) ON [PRIMARY]
GO
and table 2:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CV3Order]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CV3Order]
GO
CREATE TABLE [dbo].[CV3Order] (
[SiteID] [smallint] NULL ,
[RepFlags] [tinyint] NULL ,
[Build] [int] NOT NULL ,
[TouchedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TouchedWhen] [datetime] NULL ,
[CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreatedWhen] [datetime] NULL ,
[Active] [bit] NOT NULL ,
[GUID] [HVCIDdt] NOT NULL ,
[Entered] [datetime] NULL ,
[Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ToBeVerified] [bit] NOT NULL ,
[ToBeSigned] [bit] NOT NULL ,
[EnterRole] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserGUID] [HVCIDdt] NULL ,
[ClientVisitGUID] [numeric](16, 0) NOT NULL ,
[ClientGUID] [HVCIDdt] NULL ,
[ChartGUID] [HVCIDdt] NULL ,
[Name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Modifier] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequestedDtm] [datetime] NULL ,
[RequestedDate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequestedTime] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StopDtm] [datetime] NULL ,
[StopDate] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StopTime] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Duration] [int] NOT NULL ,
[IsDurationChangeable] [bit] NOT NULL ,
[PerformedDtm] [datetime] NULL ,
[SignificantDtm] [datetime] NULL ,
[SignificantTime] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[ScheduledDtm] [datetime] NULL ,
[AncillaryReferenceCode] [varchar] (75) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[SummaryLine] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IDCode] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TypeCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApplicSource] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsForDischarge] [bit] NOT NULL ,
[HasBeenModified] [bit] NOT NULL ,
[IsConditional] [bit] NOT NULL ,
[ConditionsText] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IsHeld] [bit] NOT NULL ,
[HoldReasonText] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[IsAutoActivatable] [bit] NOT NULL ,
[ActivateDateReference] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ActivateDaysBefore] [int] NOT NULL ,
[ActivateHoursBefore] [int] NOT NULL ,
[ActivateStatus] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumStatusForActivation] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IsAutoCompletable] [bit] NOT NULL ,
[CompleteDateReference] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[CompleteDaysAfter] [int] NOT NULL ,
[CompleteHoursAfter] [int] NOT NULL ,
[CompleteStatus] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumStatusForCompletion] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IsCompleteTemplate] [bit] NOT NULL ,
[TranspMethodCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[FrequencyCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SourceCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderPriorityCode] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
...
read more »