Microsoft KB Archive/320603

= FIX: Distribution Agent May Experience an Access Violation in a Republisher Topology =

Article ID: 320603

Article Last Modified on 10/9/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q320603



BUG #: 357035 (SHILOH_BUGS)



SYMPTOMS
When you use transactional replication in a republishing topology, the Distribution Agent may experience an Access Violation (AV), which leads to an abnormal termination of the Distribution Agent.

When the Access Violation occurs on a server that is running Microsoft SQL Server 2000 Service Pack 2, the following stack dump is generated: *******************************************************

An exception was caught in DISTRIB.EXE


 * Exception Address = 7800209a
 * Exception Code = c0000005

Module Name Return Address Location MSVCRT     00403a45        wcsncpy + 14 distrib    0040104c        00403a45 distrib    77e8d326        0040104c KERNEL32       00000000        CreateProcessW + 900



CAUSE
With transactional replication, the Logreader Agent invokes the sp_MSadd_replcommands27hp stored procedure to insert the log records from the publisher to the distribution database. The Distribution Agent then takes these log records and distributes them to the subscriber.

In a republishing topology, the sp_MSadd_replcommands27hp stored procedure may incorrectly insert an empty command (0x) in the MSRepl_commands system table in the distribution database. As a result, the Distributor Agent may experience the Access Violation mentioned in the &quot;Symptoms&quot; section when it tries to handle the empty command.

'Logreader Agent'

The Logreader might send a transaction with no command to distribution, with the intent being to update the MSrepl_transactions system table properly so that the next scan does not repeat the same section of the log.

The Distribution Agent does not expect an empty command; therefore, the Logreader checks the length of &quot;command&quot; in the sp_MSadd_repl_commands27hp stored procedure to make sure that the xact_seqno column is inserted in the MSRepl_transactions system table, but not in the MSRepl_commands system table.

The problem is that the &quot;length checking&quot; logic does not take into account a republishing or bi-directional scenario, where the originator srv and the db fields are not empty. It only checks to see if datalength(@data) > 39 to determine if the command is empty. However, it does not test the actual command data length(@cmd_data_len) before it inserts data in the MSRepl_commands system table.

'Distribution Agent'

The following data was gathered from the command that was causing the Access Violation: xact_seqno                        type        article_id originator_id command_id command        cmd_data_len -- --- -- - -- - 0x000770C1000000F60001             0           0x00000000 NULL          0x00000001 0x             0 The Distribution Agent uses the sp_MSget_repl_commands stored procedure to select the commands that it needs to process. The sp_MSget_repl_commands stored procedure only picks up a command with article_id=0 when all of the following conditions are true:
 * There are no inactive subscriptions.
 * Loopback detection is not turned on for any articles.
 * The number of articles in the MSsubscriptions system table is the same as the number of articles in the MSarticles system table for this particular publication.

When the record with article_id=0 is picked up, the command (0x) is passed to the Distribution Agent; however, it is not handled gracefully and the Access Violation occurs.

To summarize, the cause of the problem is that:


 * The Logreader inserts a command of 0x in the MSRepl_commands system table in the distribution database. -and-


 * The Distribution Agent picks up commands with article_id=0 from the MSRepl_commands system table in the Distribution database.



RESOLUTION
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

NOTE: The following hotfix was created before the release of Microsoft SQL Server 2000 Service Pack 3.



WORKAROUND
To work around this behavior and avoid the Access Violation in the Distribution Agent, specify that @loopback_detection = 'true' by using the sp_addsubscription stored procedure. When you use the @loopback_detection = 'true' option, the Distribution Agent avoids picking up commands with article_id=0, thereby avoiding the Access Violation.

For additional information about a documentation bug with @loopback_detection, click the article number below to view the article in the Microsoft Knowledge Base:

321852 DOC: Loopback_detection Option is Not Clearly Explained in SQL Server Books Online



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

Keywords: kbbug kbpending kbsqlserv2000sp3fix KB320603

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.