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.
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:
SQL Server 2008
SQL Server 2008Important 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:
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:
Method 1To 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.
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 2Use 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.
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:
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:
How to obtain the latest service pack for SQL Server 2005
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:
New naming schema for Microsoft SQL Server software update packages
Description of the standard terminology that is used to describe Microsoft software updates
Article ID: 973103 - Last Review: Sep 22, 2009 - Revision: 1