Skipping Errors in Transactional Replication on SQLSERVER 20

发布时间：2020-12-12 13:44:30 所属栏目：MsSql教程来源：网络整理

导读：玩过Oracle Golden Gate的网友都知道，OGG在配置参数文件中，可以通过增加ignore error的参数临时绕过因一些特定错误引起的复制中断。 Sqlserver Replication 也是一样。SQLSERVER 提供了SKIPERRORS这个参数可以忽略此类错误： https://technet.microsoft.co

玩过Oracle Golden Gate的网友都知道，OGG在配置参数文件中，可以通过增加ignore error的参数临时绕过因一些特定错误引起的复制中断。

Sqlserver Replication 也是一样。SQLSERVER 提供了SKIPERRORS这个参数可以忽略此类错误：

https://technet.microsoft.com/en-us/library/ms151331.aspx

For transactional replication,there are two ways to skip errors if they are encountered during the distribution process:

The?-SkipErrors?parameter of the Distribution Agent,which allows you to skip errors of a certain type. The transaction with the error is not committed,but subsequent transactions are.
The?sp_setsubscriptionxactseqno?stored procedure,which allows you to skip one or more transactions that cause errors. This option is not available for non-SQL Server Subscribers

The -SkipErrors Parameter

By default,when the Distribution Agent encounters an error,the agent stops. If you use the?-SkipErrors?parameter,and specify expected errors or errors that you do not want to interfere with replication,the agent will log the error information and then continue running. For example,if you want to specify that the Distribution Agent should log duplicate key violations but continue to process subsequent transactions,specify that the agent should skip errors 2601 (Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.) and 2627 (Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.):

-SkipErrors 2601:2627 ? //如果需要忽略2601和2627 2个错误，中间用冒号间隔

The most common way to use the?-SkipErrors?parameter is to use the Distribution Agent profile titled?Continue On Data Consistency Errors. The Distribution Agent will then skip errors 2601,2627,and 20598 (The row was not found at the Subscriber when applying the replicated command). For more information,see?Replication Agent Profiles. In addition to this predefined profile,you can specify the parameter in an agent profile you create or modify,or on the command line. For more information

比如当遇到SQLSERVER 往Oracle复制出现的下图这类报错（即错误号为969）：

Workaround方法：通过distribution job加上 -skiperrors 969并重启该JOB即可绕过该问题（下图忽略969和1400 2个错误）。

另针对ORA 1400错误，是由于SQLSERVER 字段类型为not null，但是在对null值得处理时，依然允许empty值插入。但对于Oracle而言，只要设置成null，就不允许empty值进来：

Oracle treats both empty strings and NULL values as NULL. This is important if you define a SQL Server column as NOT NULL,and are replicating the column to an Oracle Subscriber.?
To avoid failures when applying changes to the Oracle Subscriber,you must do one of the following:? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
1）Ensure that empty strings are not inserted into the published table as column values. ?? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
2）Use the –SkipErrors parameter for the Distribution Agent if it is acceptable to be notified of failures in the Distribution Agent history log and to continue processing.?Specify the Oracle error code 1400 (-SkipErrors1400). ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
3）Modify the generated create table script,removing the NOT NULL attribute from any character columns that may have associated empty strings,?
and supply the modified script as a custom create script for the article using the @creation_script parameter of sp_addarticle. ? ? ? ? ? ? ? ? ?
Oracle Subscribers support a schema option of 0x4071. For more information about schema options,see sp_addarticle (Transact-SQL). ? ? ? ? ? ?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!