Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: Updating Across a Partitioned View

    This post concerns updating across a partitioned view, and not unlike others about this subject Iím getting this error:

    Msg 4436, Level 16, State 12, Line 1
    UNION ALL view 'dbII.dbo.MyTable' is not updatable because a partitioning column was not found.


    Iím aware of the rules for defining a partitioning column, but interpreting them may have beaten me. So perhaps I havenít abided by all the rules Ė PLEASE can you spot which one(s) from the view and table definitions? I suspect the CHECK constraint does not allow the ASCII function, but I canít see how to avoid using it given SYSCODE entries in one table are like Ď[A-Z]%í and in the other are like Ď[0-9]%í.

    Otherwise, I suspect it is because one of the tables has, by legacy, a text column and the view is casting it to varchar(MAX). I also suspect it is because thereís a second column with a unique index. These arenít mentioned in the rules (are they?).

    Hereís the view definition:

    SELECT SYSCODE, COL2, CAST(COMMENTS AS varchar(MAX)) AS COMMENTS
    FROM dbo.MYTABLE
    UNION ALL
    SELECT SYSCODE, COL2, COMMENTS
    FROM OTHERDATABASE.dbo.MYTABLE AS MYTABLE_1

    And here are the table definitions:

    -- Table in the database where view is defined
    CREATE TABLE [dbo].[MYTABLE](
    [SYSCODE] [char](12) NOT NULL,
    [COL2] [char](20) NOT NULL,
    [COMMENTS] [text] NULL,
    CONSTRAINT [PK_MYTABLE] PRIMARY KEY NONCLUSTERED
    (
    [SYSCODE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    CONSTRAINT [COL2Idx] UNIQUE CLUSTERED
    (
    [COMMENTS] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    ALTER TABLE [dbo].[MYTABLE] WITH CHECK ADD CONSTRAINT [CK_MYTABLE] CHECK ((ascii([SysCode])<(65)))
    GO
    ALTER TABLE [dbo].[MYTABLE] CHECK CONSTRAINT [CK_MYTABLE]

    -- Table in the other database
    CREATE TABLE [dbo].[MYTABLE](
    [SYSCODE] [char](12) NOT NULL,
    [COL2] [char](20) NOT NULL,
    [COMMENTS] [varchar(MAX)] NULL,
    CONSTRAINT [PK_MYTABLE] PRIMARY KEY NONCLUSTERED
    (
    [SYSCODE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
    CONSTRAINT [COL2Idx] UNIQUE CLUSTERED
    (
    [COL2] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    ALTER TABLE [dbo].[MYTABLE] WITH CHECK ADD CONSTRAINT [CK_MYTABLE] CHECK ((ascii([SysCode])>=(65)))
    GO
    ALTER TABLE [dbo].[MYTABLE] CHECK CONSTRAINT [CK_MYTABLE]

  2. #2
    Join Date
    Mar 2012
    Posts
    2

    Solved

    Simplification of the CHECK constraint worked:
    In one table it is now: ([SysCode]<'A')
    In the other it is now: ([SysCode]>='A')
    This, thanks to Gianluca Sartori

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •