BUG: Distribution Agent Fails with "Column name or number of supplied values does not match table definition" Error (250623)

The information in this article applies to:

Microsoft SQL Server 7.0

This article was previously published under Q250623

BUG #: 57002 (SQLBUG_70)

SYMPTOMS

In transactional replication, the distribution agent may fail with the following error even after you select the Use column names in SQL statements option:

213: Column name or number of supplied values does not match table definition.

The following circumstances may cause the error to occur:

The publication is created to support heterogeneous subscribers.
The table schema is different between publisher and subscriber.

CAUSE

In the preceding circumstances, SQL statements are being replicated to the subscriber, without using a column list in the INSERT statement, which then results in the 213 error.

WORKAROUND

Here are two ways to work around this problem:

Method One
1. Go to Publication Properties window.
2. Click the Articles tab.
3. Click the ellipse button (...) to the right of the replicated table, and then click the Commands tab.
4. Next, select the Use column names in SQL Statements box.
5. Now, unsubscribe and resubscribe.
Method Two Use sp_changearticle to make the change and then unsubscribe and resubscribe. For example:
```
exec sp_changearticle N'pubs', N'table1', 'status', 'include column
names'
					
```

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0.

MORE INFORMATION

In transactional replication you use the Use column names in SQL statements option on an article when the article schema is different between the publisher and the subscriber. For example, the subscriber may have some extra columns or some columns that are missing in the subscriber's table. Consider this table structure:

CREATE TABLE t1
(
i int,
j int,
k int,
l int
)

If you have set the Use column names in SQL statements option for that article, the INSERT statement is replicated as follows:

INSERT t1 (i) VALUES (1)

If you have not set the Use column names in SQL statements property for that article (the default), the INSERT statement is replicated like this:

INSERT t1 VALUES(1)

Modification Type:	Major	Last Reviewed:	10/16/2002
Keywords:	kbBug KB250623