sqlserver复制在订阅端跳过错误
sqlserver复制在订阅端跳过错误
不用拆啊，找到出错那个事务号，在订阅端跳过就好了
在发布端添加一个PR列，在订阅端已经有PR这一个列了，所以复制出错在复制监视器里
参考陈景的文章：http://blog.sina.com.cn/s/blog_3eb222740100stz7.html
---------------跳过订阅机器上面的错误------------------------ -----------------------在分发机器上-------------------------- --语法 sp_helpsubscriptionerrors [ @publisher = ]'publisher' , [ @publisher_db = ]'publisher_db' , [ @publication = ]'publication' , [ @subscriber = ]'subscriber' , [ @subscriber_db = ]'subscriber_db' --get publisher subscriber select*from MSsubscriber_info --get publisher_db publication subscriber_db=publisher_db select*from MSpublications --example sp_helpsubscriptionerrors 'SQLw2k8','dbtranpub','dbtranpub_pub','SQLw2k8Subscriber','dbtransub' --获取xact_seqno 值 ----------------------在订阅机器上--------------------------- sp_setsubscriptionxactseqno [ @publisher= ]'publisher', [ @publisher_db= ]'publisher_db', [ @publication= ]'publication', [ @xact_seqno= ] xact_seqno --example sp_helpsubscriptionerrors 'SQLw2k8','dbtranpub','dbtranpub_pub',xact_seqno -------------------------------------------------------------------------------------
将command列复制出来然后在一级订阅执行，不用在门05上执行
--1、在分发库看复制监视器或者使用下面的存储过程查看哪个事务卡住了 sp_helpsubscriptionerrors 'SQLw2k8','dbtranpub','dbtranpub_pub','SQLw2k8Subscriber','dbtransub' --2、在订阅端执行 exec sp_setsubscriptionxactseqno @publisher= 'TZR06\SQLSERVER2008R2', @publisher_db= 'TASK', @publication= 'PUB_TASK_MENTOUGOU04', @xact_seqno= 0x0004C9E70002D433000100000000 --卡住的事务号 --3、把第一步sp_helpsubscriptionerrors卡住的那条命令开始的第二条命令在订阅库里执行一下，复制正常 --ALTER TABLE [dbo].[Basic] ADD [PR] int NULL CONSTRAINT [DF__Basic__PR__69E6AD86] DEFAULT ((0)) if object_id(N'[dbo].[sp_MSins_dboBasic]', 'P') > 0 drop proc [dbo].[sp_MSins_dboBasic] -- if object_id(N'dbo.MSreplication_objects') is not null delete from dbo.MSreplication_objects where object_name = N'sp_MSins_dboBasic' -- create procedure [dbo].[sp_MSins_dboBasic] @c1 int, @c2 nvarchar(50), @c3 bit, @c4 tinyint, @c5 int, @c6 bit, @c7 int as begin insert into [dbo].[Basic]( [ID], [Name], [Overseas], [Category], [GroupID], [Delete], [PR] ) values ( @c1, @c2, @c3, @c4, @c5, @c6, @c7 ) end -- if columnproperty(object_id(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') is not null exec sp_executesql @statement = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (@object_name, @publisher, @publisher_db, @publication, @article, ''P'')', @parameters = N'@object_name sysname, @publisher sysname, @publisher_db sysname, @publication sysname, @article sysname', @object_n ame = N'sp_MSins_dboBasic', @publisher = N'TZR06\SQLSERVER2008R2', @publisher_db = N'Task', @publication = N'SiteInfo', @article = N'Basic' -- --
在订阅端执行下面的SQL语句
--ALTER TABLE [dbo].[Basic] ADD [PR] INT NULL CONSTRAINT [DF__Basic__PR__69E6AD86] DEFAULT ((0)) IF OBJECT_ID(N'[dbo].[sp_MSins_dboBasic]', 'P') > 0 DROP PROC [dbo].[sp_MSins_dboBasic] -- IF OBJECT_ID(N'dbo.MSreplication_objects') IS NOT NULL DELETE FROM dbo.MSreplication_objects WHERE object_name = N'sp_MSins_dboBasic' -- CREATE PROCEDURE [dbo].[sp_MSins_dboBasic] @c1 int , @c2 nvarchar(50) , @c3 bit , @c4 tinyint , @c5 int , @c6 bit , @c7 int AS BEGIN INSERT INTO [dbo].[Basic] ( [ID] , [Name] , [Overseas] , [Category] , [GroupID] , [Delete] , [PR] ) VALUES ( @c1 , @c2 , @c3 , @c4 , @c5 , @c6 , @c7 ) END -- IF COLUMNPROPERTY(OBJECT_ID(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') IS NOT NULL EXEC sp_executesql @statement = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (@object_name, @publisher, @publisher_db, @publication, @article, ''P'')', @parameters = N'@object_name sysname, @publisher sysname, @publisher_db sysname, @publication sysname, @article sysname', @object_name = N'sp_MSins_dboBasic', @publisher = N'TZR06\SQLSERVER2008R2', @publisher_db = N'Task', @publication = N'SiteInfo', @article = N'Basic' -- -- IF OBJECT_ID(N'[dbo].[sp_MSdel_dboBasic]', 'P') > 0 DROP PROC [dbo].[sp_MSdel_dboBasic] -- IF OBJECT_ID(N'dbo.MSreplication_objects') IS NOT NULL DELETE FROM dbo.MSreplication_objects WHERE object_name = N'sp_MSdel_dboBasic' -- CREATE PROCEDURE [dbo].[sp_MSdel_dboBasic] @pkc1 int AS BEGIN DELETE [dbo].[Basic] WHERE [ID] = @pkc1 IF @@rowcount = 0 IF @@microsoftversion > 0x07320000 EXEC sp_MSreplraiserror 20598 END -- IF COLUMNPROPERTY(OBJECT_ID(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') IS NOT NULL EXEC sp_executesql @statement = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (@object_name, @publisher, @publisher_db, @publication, @article, ''P'')', @parameters = N'@object_name sysname, @publisher sysname, @publisher_db sysname, @publication sysname, @article sysname', @object_name = N'sp_MSdel_dboBasic', @publisher = N'TZR06\SQLSERVER2008R2', @publisher_db = N'Task', @publication = N'SiteInfo', @article = N'Basic' -- -- IF OBJECT_ID(N'[dbo].[sp_MSupd_dboBasic]', 'P') > 0 DROP PROC [dbo].[sp_MSupd_dboBasic] -- IF OBJECT_ID(N'dbo.MSreplication_objects') IS NOT NULL DELETE FROM dbo.MSreplication_objects WHERE object_name = N'sp_MSupd_dboBasic' -- CREATE PROCEDURE [dbo].[sp_MSupd_dboBasic] @c1 int = NULL , @c2 nvarchar(50) = NULL , @c3 bit = NULL , @c4 tinyint = NULL , @c5 int = NULL , @c6 bit = NULL , @c7 int = NULL , @pkc1 int = NULL , @bitmap binary(1) AS BEGIN IF ( SUBSTRING(@bitmap, 1, 1) & 1 = 1 ) BEGIN UPDATE [dbo].[Basic] SET [ID] = CASE SUBSTRING(@bitmap, 1, 1) & 1 WHEN 1 THEN @c1 ELSE [ID] END , [Name] = CASE SUBSTRING(@bitmap, 1, 1) & 2 WHEN 2 THEN @c2 ELSE [Name] END , [Overseas] = CASE SUBSTRING(@bitmap, 1, 1) & 4 WHEN 4 THEN @c3 ELSE [Overseas] END , [Category] = CASE SUBSTRING(@bitmap, 1, 1) & 8 WHEN 8 THEN @c4 ELSE [Category] END , [GroupID] = CASE SUBSTRING(@bitmap, 1, 1) & 16 WHEN 16 THEN @c5 ELSE [GroupID] END , [Delete] = CASE SUBSTRING(@bitmap, 1, 1) & 32 WHEN 32 THEN @c6 ELSE [Delete] END , [PR] = CASE SUBSTRING(@bitmap, 1, 1) & 64 WHEN 64 THEN @c7 ELSE [PR] END WHERE [ID] = @pkc1 IF @@rowcount = 0 IF @@microsoftversion > 0x07320000 EXEC sp_MSreplraiserror 20598 END ELSE BEGIN UPDATE [dbo].[Basic] SET [Name] = CASE SUBSTRING(@bitmap, 1, 1) & 2 WHEN 2 THEN @c2 ELSE [Name] END , [Overseas] = CASE SUBSTRING(@bitmap, 1, 1) & 4 WHEN 4 THEN @c3 ELSE [Overseas] END , [Category] = CASE SUBSTRING(@bitmap, 1, 1) & 8 WHEN 8 THEN @c4 ELSE [Category] END , [GroupID] = CASE SUBSTRING(@bitmap, 1, 1) & 16 WHEN 16 THEN @c5 ELSE [GroupID] END , [Delete] = CASE SUBSTRING(@bitmap, 1, 1) & 32 WHEN 32 THEN @c6 ELSE [Delete] END , [PR] = CASE SUBSTRING(@bitmap, 1, 1) & 64 WHEN 64 THEN @c7 ELSE [PR] END WHERE [ID] = @pkc1 IF @@rowcount = 0 IF @@microsoftversion > 0x07320000 EXEC sp_MSreplraiserror 20598 END END -- IF COLUMNPROPERTY(OBJECT_ID(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') IS NOT NULL EXEC sp_executesql @statement = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (@object_name, @publisher, @publisher_db, @publication, @article, ''P'')', @parameters = N'@object_name sysname, @publisher sysname, @publisher_db sysname, @publication sysname, @article sysname', @object_name = N'sp_MSupd_dboBasic', @publisher = N'TZR06\SQLSERVER2008R2', @publisher_db = N'Task', @publication = N'SiteInfo', @article = N'Basic' -- -- --ALTER TABLE [dbo].[Basic] ADD [PR] INT NULL CONSTRAINT [DF__Basic__PR__69E6AD86] DEFAULT ((0)) IF OBJECT_ID(N'[dbo].[sp_MSins_dboBasic]', 'P') > 0 DROP PROC [dbo].[sp_MSins_dboBasic] -- IF OBJECT_ID(N'dbo.MSreplication_objects') IS NOT NULL DELETE FROM dbo.MSreplication_objects WHERE object_name = N'sp_MSins_dboBasic' -- CREATE PROCEDURE [dbo].[sp_MSins_dboBasic] @c1 int , @c2 nvarchar(50) , @c3 varchar(1000) , @c4 bit , @c5 tinyint , @c6 tinyint , @c7 int , @c8 tinyint , @c9 tinyint , @c10 varchar(360) , @c11 varchar(360) , @c12 bit , @c13 int , @c14 int , @c15 int , @c16 int , @c17 int , @c18 int , @c19 int , @c20 nvarchar(100) , @c21 smalldatetime , @c22 bit , @c23 bit , @c24 int AS BEGIN INSERT INTO [dbo].[Basic] ( [ID] , [Name] , [Url] , [Overseas] , [Category] , [Language] , [Interval] , [Threads] , [Depth] , [TextMask] , [LinkMask] , [Block] , [ExtensionID] , [CustomID] , [GroupID] , [SiteID] , [LabelID] , [ArchiveID] , [HistoryID] , [Description] , [AddOn] , [Pause] , [Delete] , [PR] ) VALUES ( @c1 , @c2 , @c3 , @c4 , @c5 , @c6 , @c7 , @c8 , @c9 , @c10 , @c11 , @c12 , @c13 , @c14 , @c15 , @c16 , @c17 , @c18 , @c19 , @c20 , @c21 , @c22 , @c23 , @c24 ) END -- IF COLUMNPROPERTY(OBJECT_ID(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') IS NOT NULL EXEC sp_executesql @statement = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (@object_name, @publisher, @publisher_db, @publication, @article, ''P'')', @parameters = N'@object_name sysname, @publisher sysname, @publisher_db sysname, @publication sysname, @article sysname', @object_name = N'sp_MSins_dboBasic', @publisher = N'TZR06\SQLSERVER2008R2', @publisher_db = N'Task', @publication = N'pub_Task_MenTouGou04', @article = N'Basic' -- -- IF OBJECT_ID(N'[dbo].[sp_MSdel_dboBasic]', 'P') > 0 DROP PROC [dbo].[sp_MSdel_dboBasic] -- IF OBJECT_ID(N'dbo.MSreplication_objects') IS NOT NULL DELETE FROM dbo.MSreplication_objects WHERE object_name = N'sp_MSdel_dboBasic' -- CREATE PROCEDURE [dbo].[sp_MSdel_dboBasic] @pkc1 int AS BEGIN DELETE [dbo].[Basic] WHERE [ID] = @pkc1 IF @@rowcount = 0 IF @@microsoftversion > 0x07320000 EXEC sp_MSreplraiserror 20598 END -- IF COLUMNPROPERTY(OBJECT_ID(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') IS NOT NULL EXEC sp_executesql @statement = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (@object_name, @publisher, @publisher_db, @publication, @article, ''P'')', @parameters = N'@object_name sysname, @publisher sysname, @publisher_db sysname, @publication sysname, @article sysname', @object_name = N'sp_MSdel_dboBasic', @publisher = N'TZR06\SQLSERVER2008R2', @publisher_db = N'Task', @publication = N'pub_Task_MenTouGou04', @article = N'Basic' -- -- IF OBJECT_ID(N'[dbo].[sp_MSupd_dboBasic]', 'P') > 0 DROP PROC [dbo].[sp_MSupd_dboBasic] -- IF OBJECT_ID(N'dbo.MSreplication_objects') IS NOT NULL DELETE FROM dbo.MSreplication_objects WHERE object_name = N'sp_MSupd_dboBasic' -- CREATE PROCEDURE [dbo].[sp_MSupd_dboBasic] @c1 int = NULL , @c2 nvarchar(50) = NULL , @c3 varchar(1000) = NULL , @c4 bit = NULL , @c5 tinyint = NULL , @c6 tinyint = NULL , @c7 int = NULL , @c8 tinyint = NULL , @c9 tinyint = NULL , @c10 varchar(360) = NULL , @c11 varchar(360) = NULL , @c12 bit = NULL , @c13 int = NULL , @c14 int = NULL , @c15 int = NULL , @c16 int = NULL , @c17 int = NULL , @c18 int = NULL , @c19 int = NULL , @c20 nvarchar(100) = NULL , @c21 smalldatetime = NULL , @c22 bit = NULL , @c23 bit = NULL , @c24 int = NULL , @pkc1 int = NULL , @bitmap binary(3) AS BEGIN IF ( SUBSTRING(@bitmap, 1, 1) & 1 = 1 ) BEGIN UPDATE [dbo].[Basic] SET [ID] = CASE SUBSTRING(@bitmap, 1, 1) & 1 WHEN 1 THEN @c1 ELSE [ID] END , [Name] = CASE SUBSTRING(@bitmap, 1, 1) & 2 WHEN 2 THEN @c2 ELSE [Name] END , [Url] = CASE SUBSTRING(@bitmap, 1, 1) & 4 WHEN 4 THEN @c3 ELSE [Url] END , [Overseas] = CASE SUBSTRING(@bitmap, 1, 1) & 8 WHEN 8 THEN @c4 ELSE [Overseas] END , [Category] = CASE SUBSTRING(@bitmap, 1, 1) & 16 WHEN 16 THEN @c5 ELSE [Category] END , [Language] = CASE SUBSTRING(@bitmap, 1, 1) & 32 WHEN 32 THEN @c6 ELSE [Language] END , [Interval] = CASE SUBSTRING(@bitmap, 1, 1) & 64 WHEN 64 THEN @c7 ELSE [Interval] END , [Threads] = CASE SUBSTRING(@bitmap, 1, 1) & 128 WHEN 128 THEN @c8 ELSE [Threads] END , [Depth] = CASE SUBSTRING(@bitmap, 2, 1) & 1 WHEN 1 THEN @c9 ELSE [Depth] END , [TextMask] = CASE SUBSTRING(@bitmap, 2, 1) & 2 WHEN 2 THEN @c10 ELSE [TextMask] END , [LinkMask] = CASE SUBSTRING(@bitmap, 2, 1) & 4 WHEN 4 THEN @c11 ELSE [LinkMask] END , [Block] = CASE SUBSTRING(@bitmap, 2, 1) & 8 WHEN 8 THEN @c12 ELSE [Block] END , [ExtensionID] = CASE SUBSTRING(@bitmap, 2, 1) & 16 WHEN 16 THEN @c13 ELSE [ExtensionID] END , [CustomID] = CASE SUBSTRING(@bitmap, 2, 1) & 32 WHEN 32 THEN @c14 ELSE [CustomID] END , [GroupID] = CASE SUBSTRING(@bitmap, 2, 1) & 64 WHEN 64 THEN @c15 ELSE [GroupID] END , [SiteID] = CASE SUBSTRING(@bitmap, 2, 1) & 128 WHEN 128 THEN @c16 ELSE [SiteID] END , [LabelID] = CASE SUBSTRING(@bitmap, 3, 1) & 1 WHEN 1 THEN @c17 ELSE [LabelID] END , [ArchiveID] = CASE SUBSTRING(@bitmap, 3, 1) & 2 WHEN 2 THEN @c18 ELSE [ArchiveID] END , [HistoryID] = CASE SUBSTRING(@bitmap, 3, 1) & 4 WHEN 4 THEN @c19 ELSE [HistoryID] END , [Description] = CASE SUBSTRING(@bitmap, 3, 1) & 8 WHEN 8 THEN @c20 ELSE [Description] END , [AddOn] = CASE SUBSTRING(@bitmap, 3, 1) & 16 WHEN 16 THEN @c21 ELSE [AddOn] END , [Pause] = CASE SUBSTRING(@bitmap, 3, 1) & 32 WHEN 32 THEN @c22 ELSE [Pause] END , [Delete] = CASE SUBSTRING(@bitmap, 3, 1) & 64 WHEN 64 THEN @c23 ELSE [Delete] END , [PR] = CASE SUBSTRING(@bitmap, 3, 1) & 128 WHEN 128 THEN @c24 ELSE [PR] END WHERE [ID] = @pkc1 IF @@rowcount = 0 IF @@microsoftversion > 0x07320000 EXEC sp_MSreplraiserror 20598 END ELSE BEGIN UPDATE [dbo].[Basic] SET [Name] = CASE SUBSTRING(@bitmap, 1, 1) & 2 WHEN 2 THEN @c2 ELSE [Name] END , [Url] = CASE SUBSTRING(@bitmap, 1, 1) & 4 WHEN 4 THEN @c3 ELSE [Url] END , [Overseas] = CASE SUBSTRING(@bitmap, 1, 1) & 8 WHEN 8 THEN @c4 ELSE [Overseas] END , [Category] = CASE SUBSTRING(@bitmap, 1, 1) & 16 WHEN 16 THEN @c5 ELSE [Category] END , [Language] = CASE SUBSTRING(@bitmap, 1, 1) & 32 WHEN 32 THEN @c6 ELSE [Language] END , [Interval] = CASE SUBSTRING(@bitmap, 1, 1) & 64 WHEN 64 THEN @c7 ELSE [Interval] END , [Threads] = CASE SUBSTRING(@bitmap, 1, 1) & 128 WHEN 128 THEN @c8 ELSE [Threads] END , [Depth] = CASE SUBSTRING(@bitmap, 2, 1) & 1 WHEN 1 THEN @c9 ELSE [Depth] END , [TextMask] = CASE SUBSTRING(@bitmap, 2, 1) & 2 WHEN 2 THEN @c10 ELSE [TextMask] END , [LinkMask] = CASE SUBSTRING(@bitmap, 2, 1) & 4 WHEN 4 THEN @c11 ELSE [LinkMask] END , [Block] = CASE SUBSTRING(@bitmap, 2, 1) & 8 WHEN 8 THEN @c12 ELSE [Block] END , [ExtensionID] = CASE SUBSTRING(@bitmap, 2, 1) & 16 WHEN 16 THEN @c13 ELSE [ExtensionID] END , [CustomID] = CASE SUBSTRING(@bitmap, 2, 1) & 32 WHEN 32 THEN @c14 ELSE [CustomID] END , [GroupID] = CASE SUBSTRING(@bitmap, 2, 1) & 64 WHEN 64 THEN @c15 ELSE [GroupID] END , [SiteID] = CASE SUBSTRING(@bitmap, 2, 1) & 128 WHEN 128 THEN @c16 ELSE [SiteID] END , [LabelID] = CASE SUBSTRING(@bitmap, 3, 1) & 1 WHEN 1 THEN @c17 ELSE [LabelID] END , [ArchiveID] = CASE SUBSTRING(@bitmap, 3, 1) & 2 WHEN 2 THEN @c18 ELSE [ArchiveID] END , [HistoryID] = CASE SUBSTRING(@bitmap, 3, 1) & 4 WHEN 4 THEN @c19 ELSE [HistoryID] END , [Description] = CASE SUBSTRING(@bitmap, 3, 1) & 8 WHEN 8 THEN @c20 ELSE [Description] END , [AddOn] = CASE SUBSTRING(@bitmap, 3, 1) & 16 WHEN 16 THEN @c21 ELSE [AddOn] END , [Pause] = CASE SUBSTRING(@bitmap, 3, 1) & 32 WHEN 32 THEN @c22 ELSE [Pause] END , [Delete] = CASE SUBSTRING(@bitmap, 3, 1) & 64 WHEN 64 THEN @c23 ELSE [Delete] END , [PR] = CASE SUBSTRING(@bitmap, 3, 1) & 128 WHEN 128 THEN @c24 ELSE [PR] END WHERE [ID] = @pkc1 IF @@rowcount = 0 IF @@microsoftversion > 0x07320000 EXEC sp_MSreplraiserror 20598 END END -- IF COLUMNPROPERTY(OBJECT_ID(N'dbo.MSreplication_objects'), N'article', 'AllowsNull') IS NOT NULL EXEC sp_executesql @statement = N'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values (@object_name, @publisher, @publisher_db, @publication, @article, ''P'')', @parameters = N'@object_name sysname, @publisher sysname, @publisher_db sysname, @publication sysname, @article sysname', @object_name = N'sp_MSupd_dboBasic', @publisher = N'TZR06\SQLSERVER2008R2', @publisher_db = N'Task', @publication = N'pub_Task_MenTouGou04', @article = N'Basic' -- --
发布端积累百万命令
门订阅端
过来十几分钟，发布端的未分发命令数下去了
transaction replication是通过sp_repldone推进日志标志位的，如果标志位不推进，再怎么备份日志都不会截断
