Microsoft KB Archive/329185

= INF: SQL Server Replication with eEnterprise and Dynamics Product =

PSS ID Number: 329185

Article Last Modified on 10/16/2003

-

The information in this article applies to:


 * Microsoft SQL Server 2000 (all editions)
 * Microsoft SQL Server 7.0

-



This article was previously published under Q329185



SUMMARY
This article describes how Microsoft SQL Server replication performs with the Microsoft Business Solutions Great Plains eEnterprise and the Dynamics product databases.



MORE INFORMATION
The information in this section discusses the behavior and limitations of different types of SQL Server replication with the eEnterprise and Dynamics software versions (5.5, 6.0 and 7.0) databases.

One Way Replication
In one way replication, you can only update data at the publisher, not at the subscriber. To configure one way replication, you do not have to perform complex customization. For example, you do not have to remove identity columns or add a uniqueidentifier column. Both snapshot replication and one way transaction replication are one way replication types.

When one way replication is configured to replicate the eEnterprise or Dynamics products table, you do not have to modify either the eEnterprise or the Dynamic products table. Configuring one way replication should not cause any problems with the eEnterprise and Dynamics products.

Two Way Replication
In two way replication, data can be modified at both the publisher and the subscriber. Modifications performed at the publisher are propagated to the subscriber. Similarly, modifications performed at the subscriber are propagated to the publisher. When two way replication is configured, SQL Server adds a uniqueidentifier column to each table that is involved in replication. Both eEnterprise and Dynamics use identity columns in their products, and they do not allow the addition of a uniqueidentifier column.

To run the eEnterprise or the Dynamics reports on the subscriber, the identity columns must also exist at the subscriber. In two way replication, the configured identity property for the columns at the publisher are not transferred to the subscriber.

To make sure that the identity property of a column is transferred to the subscriber, follow these steps:  At the subscriber, manually create a table by using the Identity property and the NOT FOR REPLICATION option. Now you must set the Name conflicts article property so that SQL Server does not drop an existing table when it applies a snapshot to the subscriber. To set the Name conflicts property, follow these steps:  At the Publisher, open SQL Server Enterprise Manager. Expand a server group, and then click to expand the Replication folder. Expand the Publications folder. Right-click a publication, and then click Properties. In the Publication Properties dialog box, click the Articles tab.</li> Click Properties for an article.</li> In the Table Article Properties dialog box, click the Snapshot tab.</li> Click Delete all data in the existing table.</li></ol> </li></ol>

When you configure merge replication, SQL Server adds a uniqueidentifier column to the table that is used in merge replication. SQL Server also adds a uniqueidentifier column for transactional replication that has immediate updating or queued updating subscribers.

When you configure merge replication to replicate the eEnterprise or the Dynamics tables, SQL Server adds a uniqueidentifier column to the eEnterprise or the Dynamics product table. Because eEnterprise and Dynamics expect a specific number of columns in each table, the addition of a column might cause a problem. So, configuring two way replication to replicate the eEnterprise or the Dynamics product table might cause problems. When you run either eEnterprise or Dynamics, you might receive the following error messages:

Server: Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'sam' can only be specified when a column list is used and IDENTITY_INSERT is ON.

-or-

Server: Msg 213, Level 16, State 4, Line 1

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

<div class="references_section">