SQLServer 分布式分区视图插入失败,报告找不到主键[分不够再加]
在ServerHqp上执行的插入语句:
insert into h(hid,col, classid)
values(newid(), 12, 1000)
错误消息:
消息 4440,级别 16,状态 9,第 1 行
UNION ALL 视图 'RssSub.dbo.H' 不可更新,因为在表 '[SERV02].[test].[dbo].[h1_Serv02]' 上没有找到主键。
h为ServerHqp上的一个分布式分区视图:
SELECT HID, Col, ClassID
FROM SERV02.test.dbo.h1_Serv02
UNION ALL
SELECT HID, Col, ClassID
FROM SERV02.test.dbo.h2_Serv02
UNION ALL
SELECT HID, Col, ClassID
FROM dbo.H1_ServHqp
UNION ALL
SELECT HID, Col, ClassID
FROM dbo.H2_ServHqp
在ServerHqp上的两个表:
CREATE TABLE [dbo].[H1_ServHqp](
[HID] [uniqueidentifier] NOT NULL,
[Col] [bigint] NOT NULL,
[ClassID] [bigint] NULL,
CONSTRAINT [PK_H1] PRIMARY KEY CLUSTERED
(
[HID] ASC,
[Col] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [col_scheme]([Col])
) ON [col_scheme]([Col])
GO
ALTER TABLE [dbo].[H1_ServHqp] WITH CHECK ADD CHECK (([Col]<=(50)))
CREATE TABLE [dbo].[H2_ServHqp](
[HID] [uniqueidentifier] NOT NULL,
[Col] [bigint] NOT NULL,
[ClassID] [bigint] NULL,
CONSTRAINT [PK_H2] PRIMARY KEY CLUSTERED
(
[HID] ASC,
[Col] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [col_scheme]([Col])
) ON [col_scheme]([Col])
GO
ALTER TABLE [dbo].[H2_ServHqp] WITH CHECK ADD CONSTRAINT [CK__H2_ServHqp__Col__4C364F0E] CHECK (([Col]>=(51) AND [Col]<=(100)))
其中分区函数:CREATE PARTITION FUNCTION [col_partition](bigint) AS RANGE RIGHT FOR VALUES (51)
分区方案:CREATE PARTITION SCHEME [col_scheme] AS PARTITION [col_partition] TO ([second], [second])
相对应得Serv02上的表为:
CREATE TABLE [dbo].[H1_Serv02](
[HID] [uniqueidentifier] NOT NULL,
[Col] [bigint] NOT NULL,
[ClassID] [bigint] NULL,
CONSTRAINT [PK_H1] PRIMARY KEY CLUSTERED
(
[HID] ASC,
[Col] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [col_scheme]([Col])
) ON [col_scheme]([Col])
GO
ALTER TABLE [dbo].[H1_Serv02] WITH CHECK ADD CHECK (([Col]>=(101) AND [Col]<=(150)))
CREATE TABLE [dbo].[H2_Serv02](
[HID] [uniqueidentifier] NOT NULL,
[Col] [bigint] NOT NULL,
[ClassID] [bigint] NULL,
CONSTRAINT [PK_H2] PRIMARY KEY CLUSTERED
(
[HID] ASC,
[Col] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [col_scheme]([Col])
) ON [col_scheme]([Col])
GO
ALTER TABLE [dbo].[H2_Serv02] WITH CHECK ADD CHECK (([Col]>=(151)))
分区函数:CREATE PARTITION FUNCTION [col_partition](bigint) AS RANGE RIGHT FOR VALUES (151)
分区方案:CREATE PARTITION SCHEME [col_scheme] AS PARTITION [col_partition] TO ([PRIMARY], [secondary])
在servhqp上建立对serv02的链接服务器代码如下:
EXEC master.dbo.sp_addlinkedserver @server = N'SERV02', @srvproduct=N'SQLServer', @provider=N'MSDASQL', @provstr=N'DRIVER={SQL Server};SERVER=192.168.1.30;UID=sa;PWD=password;'
在网上找了也有人遇到类似的问题,但是最终都不知道解决办法,两天了都没有进展,在这里诚心求救。
SQLServer版本为2005 enterprise edition (sp1)