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)
...全文
238 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
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])

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧