Microsoft KB Archive/250623
Article ID: 250623
Article Last Modified on 10/16/2002
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q250623
BUG #: 57002 (SQLBUG_70)
In transactional replication, the distribution agent may fail with the following error even after you select the Use column names in SQL statements option:
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.
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.
Here are two ways to work around this problem:
- Method One
- Go to Publication Properties window.
- Click the Articles tab.
- Click the ellipse button (...) to the right of the replicated table, and then click the Commands tab.
- Next, select the Use column names in SQL Statements box.
- 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'
Microsoft has confirmed this to be a problem in SQL Server 7.0.
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)
Keywords: kbbug kbpending KB250623