FIX: Error message when you run the Distribution Agent to synchronize the subscribers with the publisher in Microsoft SQL Server 2005 or SQL Server 2008:" The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect"

You use transactional replication in Microsoft SQL Server 2005. When you run the Distribution Agent to synchronize the subscribers with the publisher, the Distribution Agent may fail and you receive the following error message in the Distribution Agent history or in the output log:
When these errors occur repeatedly, performance can be affected. When such a failure occurs, the Distribution Agent will do retry processing to try the same commands again. Therefore, the Distribution Agent uses more time to synchronize the subscribers with the publisher because each problematic batch is tried two times.

When the Distribution Agent processes the pending commands in the distribution database, it uses the CommitBatchSize parameter and the CommitBatchThreshold parameter to help determine how many commands are batched together as one execution when it sends those commands to the subscribers.

The CommitBatchSize parameter is the number of transactions to be issued to the subscribers before a COMMIT statement is issued. The default value is 100. The CommitBatchThreshold parameter is the number of replication commands to be issued to the subscribers before a COMMIT statement is issued. The default is 1000.

Because a single transaction can contain one command or can contain many commands, setting the CommitBatchSize parameter to 100 and setting the CommitBatchThreshold parameter to 1000 could allow for several thousand commands to be delivered in a single batch of commands.

When an article is added to a publication, the @ins_cmd, @upd_cmd, and @del_cmd parameters of the replication command type are specified for the sp_addarticle stored procedure to determine how to deliver commands to the subscriber. In addition to the CommitBatchSize and CommitBatchThreshold parameters, the Distribution Agent has to also consider that there are maximum requirements in SQL Native Client OLE DB provider on how many parameters can be bound in a single batch of commands that is sent to the subscriber.

There is a maximum of 2100 parameters that can be used in a single batch of commands. The Distribution Agent therefore automatically divides large batches into just enough statements per batch so that the maximum of 2100 parameters is not exceeded. Then, any remaining commands in the command batch are automatically moved into the next execution batch.

This problem occurs when the Distribution Agent incorrectly calculates the number of parameters that are needed to deliver a batch of commands. This happens if the Distribution Agent uses the dynamic stored procedure sp_executesql as a wrapper for a batch of many replicated commands.

If the replicated command contains more than 2098 parameters in the batch command, and the batch command is wrapped by using the sp_executesql stored procedure to deliver the batch as a dynamically built Transact-SQL statement, the Distribution Agent does not include in the calculation that the sp_executesql stored procedure needs two additional bound parameters itself to function. Therefore, the total number of parameters may exceed 2100 parameters.

The Distribution Agent may use this delivery choice of the dynamically built Transact-SQL statement wrapped with the sp_executesql stored procedure in the following situations:

• The article contains Binary Large Object (BLOB) columns such as text or image columns.
• More than one replication command type is used across all articles. For example if one article uses the SQL delivery replication command type, but another article uses the sp_MSins_procedure stored procedure as the replication command type.

SQL Server 2005

The fix for this issue was first released in Cumulative Update 5 for SQL Server 2005 Service Pack 3. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 3 hotfix to an installation of SQL Server 2005 Service Pack 3. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

SQL Server 2008

SQL Server 2008

Important You must use this fix if you are running the release version of SQL Server 2008.

The fix for this issue was first released in Cumulative Update 7. For more information about how to obtain this cumulative update package for SQL Server 2008, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

SQL Server 2008 Service Pack 1 (SP1)

Important You must use this fix if you are running SQL Server 2008 Service Pack 1 (SP1).

The fix for this issue was first released in Cumulative Update 4 for SQL Server 2008 Service Pack 1. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 1 hotfix to an installation of SQL Server 2008 Service Pack 1. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

To work around this issue, use one of the following methods:

Method 1

To help reduce occurrences of this error to a more tolerable level, decrease the value of the CommitBatchSize and CommitBatchThreshold parameters for Distribution Agent. The goal is to restrict the maximum number of commands per commit batch, to avoid exceeding 2,100 parameters per batch of commands.

For example, the default value of the CommitBatchSize parameter is 100. You can change it to 10. The default value of the CommitBatchThreshold parameter is 1000. You can change it to 100.

Decreasing these values can affect performance because batches are not delivered in the originally optimal sizes. However, this method helps avoid the error conditions that are described in the Cause section.

You can change the CommitBatchSize and CommitBatchThreshold parameters by using one of the following methods:

• Add the CommitBatchSize and CommitBatchThreshold parameters in the command for the Distribution Agent job step in SQL Server Agent
• Change the CommitBatchSize and CommitBatchThreshold parameters when you create a new agent profile from the Distributor Properties dialog box or from Replication Monitor.

For more information about how to change these parameters, visit the following Microsoft Developer Network (MSDN) Web site:


If you want to completely avoid this error in later retry of the batch, you must plan for the worst case scenario. To do this, find the widest article that has most number of columns in your publication. For example, if you have a table that has 100 columns which is published in the transactional publication, a single replicated command against that table can contain 100 parameters. You must reduce the CommitBatchThreshold parameter setting so that the maximum number of parameters that the Distribution Agent has to send in any single batch is at most 2098. If all columns are affected by the command, the CommitBatchThreshold parameter setting has to be 20 or smaller to allow for a maximum of 2000 parameters per batch of 20 commands against that table.

Method 2

Use the same replication command type for all articles in the publication when you set up the publication. Avoid mixing the command delivery mechanisms for articles in the same publication to reduce the need for the Distribution Agent to select delivering the batch by using the sp_executesql stored procedure as a wrapper on a batch of commands.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

For more information about replication Distribution Agent, visit the following Microsoft Developer Network (MSDN) Web site:

For more information about replication agent profiles, visit the following Microsoft Developer Network (MSDN) Web site:

For more information about the Incremental Servicing Model for SQL Server, click the following article number to view the article in the Microsoft Knowledge Base: For more information about how to obtain SQL Server 2005 Service Pack 3, click the following article number to view the article in the Microsoft Knowledge Base: For more information about the new features in SQL Server 2005 SP3 and about the improvements in SQL Server 2005 SP3, visit the following Microsoft Web site: For more information about the naming schema for SQL Server updates, click the following article number to view the article in the Microsoft Knowledge Base: For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base: