SQLServerWiki

“The Only Thing That Is Constant Is Change”

-SkipErrors does not record record that was skipped.

Posted by database-wiki on April 11, 2016

-SkipErrors does not record record that was skipped.

From BOL: “For example, if the Distribution Agent returns a duplicate key violation
error, but you would want the distribution process to continue and log only the
error information, you can specify the -SkipErrors command line parameter with the
number of the error that should be skipped.”

“log only the error information,” does not log the command that failed, just that
it was skipped.

Resolution:

Below is the results of testing the -SkipErrors feature on the Distribution
Agent.

I setup up my test by deleting a record at the subscriber, then updating that
record on the publisher. This generated a “row not found” error number 20598.

If I look in the Distribution Error Details available in Enterprise Manager I can
see the details on the command that was skipped.

Command: {CALL sp_MSupd_Shippers (NULL,N’Test Name Change’,NULL,4,0×02)}
Transaction sequence number and command ID of last execution batch are
0x00000006000000DF000500000000 and 1.

ERROR: The row was not found at the Subscriber when applying the replicated
command.
(Source: SQL53877 (Data source); Error number: 20598)

If I run the Distribution Agent and specify the “-Output” parameter, this same
information will be written to a text file.

[11/26/2003 11:37:31 AM]SQL53877.distribution: {call sp_MSget_repl_commands(1, ?,
0, 7500000)}
[11/26/2003 11:37:31 AM]SQL53877.TranSub: {CALL sp_MSupd_Shippers
(NULL,?,NULL,?,0x02)}
sp_MSget_repl_commands timestamp value is: 0x00000006000000df000500000000

37000 The row was not found at the Subscriber when applying the replicated
command. 20598

The details are also written to the replication system table msrepl_errors.

According to the SQL Server Books Online, the -SkipErrors will log only the error,
not the details on the record that was skipped.

From BOL: “For example, if the Distribution Agent returns a duplicate key
violation error, but you would want the distribution process to continue and log
only the error information, you can specify the –SkipErrors command line parameter
with the number of the error that should be skipped.”

Testing with -SkipErrors 20598 the Distribution Agent history only contains the
fact a record was skipped, but not the actual command.

“Skipped 1 error(s) when applying transactions at the Subscriber.”

However, the details are found in the -output file:

[11/26/2003 11:48:54 AM]SQL53877.distribution: {call sp_MSget_repl_commands(1, ?,
0, 7500000)}
[11/26/2003 11:48:54 AM]SQL53877.TranSub: {CALL sp_MSupd_Shippers
(NULL,?,NULL,?,0x02)}
sp_MSget_repl_commands timestamp value is: 0x00000006000000df000500000000
Parameterized values for above command(s): {{N’Test Name Change’, 4}}
Skipped 1 error(s) when applying transactions at the Subscriber.

The -SkipErrors is used to troubleshoot a specific problem.
During the time, adding the “-output” parameter will capture all of the details
about which records were skipped and why. This information is not kept in the
Agent History.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: