March 7, 2012 at 9:22 am
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 – can you spot which one(s) from the view and table definitions? (Please ). 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]
March 8, 2012 at 12:42 am
I suspect the problem lies on the CHECK constraint.
If it only has to separate numbers from characters, you could have a much simpler constraint:
DECLARE @a TABLE (
code varchar(10)
)
INSERT INTO @a VALUES('ABCDED')
INSERT INTO @a VALUES('TEXT')
INSERT INTO @a VALUES('NAME')
INSERT INTO @a VALUES('FOO')
INSERT INTO @a VALUES('BAR')
INSERT INTO @a VALUES('1')
INSERT INTO @a VALUES('0')
INSERT INTO @a VALUES('32423')
INSERT INTO @a VALUES('1121')
INSERT INTO @a VALUES('37346368')
SELECT *
FROM @a
WHERE code >= 'A'
SELECT *
FROM @a
WHERE code < 'A'
Could that work?
-- Gianluca Sartori
March 8, 2012 at 1:32 am
Thank you for your response SSCarpal Tunnel!
Simple is always best - it saves on key presses and helps the wrists! 🙂
I'm overjoyed to say this works. Happy Days
March 8, 2012 at 1:51 am
Great! Glad I could help.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply