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"

Article translations Article translations
Article ID: 973103 - View products that this article applies to.
Microsoft distributes Microsoft SQL Server 2005 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release.
Expand all | Collapse all

On This Page

SYMPTOMS

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:

42000 The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100. 8003

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.

CAUSE

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.

RESOLUTION

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:
972511 Cumulative update package 5 for SQL Server 2005 Service Pack 3
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:
960598 The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 3 was released
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:
973601 Cumulative update package 7 for SQL Server 2008
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:
956909 The SQL Server 2008 builds that were released after SQL Server 2008 was released

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:
973602 Cumulative update package 4 for SQL Server 2008 Service Pack 1
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:
970365 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 1 was released
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.

WORKAROUND

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:
http://msdn.microsoft.com/en-us/library/ms151326(SQL.90).aspx


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.

STATUS

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

MORE INFORMATION

For more information about replication Distribution Agent, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/ms147328(SQL.90).aspx

For more information about replication agent profiles, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/ms151223(SQL.90).aspx

REFERENCES

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:
935897 An Incremental Servicing Model is available from the SQL Server team to deliver hotfixes for reported problems
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:
913089 How to obtain the latest service pack for SQL Server 2005
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:
http://go.microsoft.com/fwlink/?LinkId=131442
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:
822499 New naming schema for Microsoft SQL Server software update packages
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:
824684 Description of the standard terminology that is used to describe Microsoft software updates

Properties

Article ID: 973103 - Last Review: August 17, 2009 - Revision: 1.2
APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
kbsurveynew kbhotfixrollup kbfix kbpubtypekc kbqfe kbexpertiseadvanced kbhotfixserver KB973103

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com