SQLServer 分布式分区视图插入失败,报告找不到主键[分不够再加]

yzx110 2006-12-13 12:53:42
在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)
...全文
158 点赞 收藏 3
写回复
3 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
caixia615 2006-12-13
指定hid为主键
回复
yzx110 2006-12-13
我不是很明白,
因为分区的依据列必须为主键的一部分,所以Col需要制定主键,
怎么指定hid为主键呢?
回复
caixia615 2006-12-13
CREATE TABLE [dbo].[H1_Serv02](
[HID] [uniqueidentifier] NOT NULL DEFAULT newid(),
[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])
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2006-12-13 12:53
社区公告
暂无公告