将列添加到复制表的事务问题
在我的软件中，我有一个运行数据库更新的进程，我需要应用这些更新来修补到我们的最新版本。在我的最新脚本中，我想向具有复制设置发布的表添加一列。当我创建更改脚本来添加列时，我收到一条消息，内容如下：
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
您知道解决此问题的最简单方法是什么吗？我是否必须编写删除出版物的脚本，然后再重新添加它！？
In my software I have a process which runs the database updates that I need to apply to patch up to our latest version. In my latest script, I want to add a column to a table that has a publication for replication setup. When I create my alter script to add the columns I get a message saying the following:
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
Any ideas what the easiest way to get round this issue is? Do I have to script a remove of the publication and then re-add it afterward!?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
本周我们在使用 SQL Compare 生成的脚本来同步我们的生产和开发环境时遇到了这个问题。该问题是由 SQL Compare（或您正在使用的任何程序）中的脚本启动隔离级别高于 READ COMMITTED 或 REPEATABLE READ 的事务引起的。
当您运行脚本的一部分时，需要使用 ALTER TABLE 添加列，这会启动对 SQL 复制代码的调用，从而将 ALTER TABLE 命令传播给订阅者。该代码无法在具有较高隔离级别的较大事务内部执行。
我们使用的解决方案是编写复制脚本。删除该表上的复制。应用同步脚本。然后重新创建复制。
另一种方法是编辑同步脚本以更改事务的隔离级别或将其完全删除。这不是我们想要做的事情，所以我们采用了前面提到的解决方案。
We had this issue this week while using scripts generated by SQL Compare to synch our production and dev environments. The issue is caused by the script from SQL Compare (or whatever you are using) starting a transaction with a higher isolation level than READ COMMITTED or REPEATABLE READ.
When you run the part of the script hat has the ALTER TABLE to add the column, that initiates a call to the SQL replication code that propogates the ALTER TABLE command to the subscribers. That code can't execute inside of the larger transaction with the higher isolation level.
The solution we used was to script out the replication. Drop the replication on that table. Apply the synch script. Then recreate the replication.
The alternative would be to edit the synch script to change the isolation level of the transaction or remove it altogether. That was not something we wanted to do so we went with the previously mentioned solution.
要将新列添加到表中并将其包含在现有发布中，您需要使用 ALTER TABLE <表>添加<栏目>出版商的语法。默认情况下，架构更改将传播到订阅者，发布属性 @replicate_ddl 必须设置为 true。
有关详细信息，请参阅对发布数据库进行架构更改。
To add a new column to a table and include it in an existing publication, you'll need to use ALTER TABLE < Table > ADD < Column > syntax at the publisher. By default the schema change will be propagated to subscribers, publication property @replicate_ddl must be set to true.
See Making Schema Changes on Publication Databases for more information.