发现一个SQLSERVER数据库的编绎解析问题
今天捧读SQLSERVER圣经联机帮助,设计SQL SERVER 2000复制时出现下面的问题,最后得出结论是SQL数据库引擎不能正确的解析或者说不是时候的解析。
1. 创建复制表:
--在发布服务器上创建表sales:
CREATE TABLE sales
(sale_id INT IDENTITY(1,1)
NOT FOR REPLICATION,
sales_region VARCHAR(20),
CONSTRAINT id_pk PRIMARY KEY (sale_id)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--在订阅服务器上创建表sales:
CREATE TABLE sales
(sale_id INT IDENTITY(100001,1)
NOT FOR REPLICATION,
sales_region VARCHAR(2),
CONSTRAINT id_pk PRIMARY KEY (sale_id)
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2. 建立事务复制,不可更新的订阅,其它都采用默认设置,只改发布属性“名称冲突”为“删除现有表中与行筛选语句相匹配的数据”,以防删除手工建立的表及标识列规则。在服务器插入新数据或删除旧数据时都正确,但修改数据时出错:
A.错误消息:{CALL sp_MSupd_sales (NULL,'afg2342342',2,0x02)}
上一次执行批处理的事务序列号和命令 ID 分别是 0x0000006100000082000500000000 和 1。
上一次执行批处理的事务序列号和命令 ID 分别是 0x0000006100000082000500000000 和 1。
B.上一条命:无法更新标识列 'sale_id'。
C.错误详细信息:无法更新标识列 'sale_id'。
(源: DEV1 (数据源); 错误代码: 8102)
(源: DEV1 (数据源); 错误代码: 8102)
3. 复制自动创建的订阅服务器的存储过程如下,我对错误处做了注释:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
最后得出一个结论:不是复制组件的问题,问题出在数据库引擎不能正确的解析或者说不是时候的解析,简单的试试下面的代码(先分析后执行):
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
专注数据库技术,学以致用。