BUG: NOT FOR REPLICATION clause causes SQL Server CE replication to fail (300597)

SYMPTOMS

A Microsoft SQL Server 2000 Windows CE Edition Subscriber produces the following error messages when you invoke the merge process:

NOTE: This first error does not occur for SQL Server CE 2.0, but the following two message do apply.

RESULT:NUMBER = 80040E14
NATIVE_ERROR = 25501, SSCE_M_QP_BADPARSE

You may also receive the following additional native error messages:

28557 (SSCE_M_UNUSABLEDATABASE):
"The database is in an unusable state. Delete it and recreate it."

28560 (SSCE_M_EXECUTEFAILED):
"OLE DB Execute Method failed; bad or invalid SQL statement"

CAUSE

If the table that is part of the publication contains constraints with the NOT FOR REPLICATION option that was enabled by using the ALTER TABLE statement, the SQL Server replication provider is not parsing out the ALTER TABLE statement when it sends the initial snapshot to SQL Server CE.

RESOLUTION

To resolve the problem, use either of these methods:

Do not create constraints with the NOT FOR REPLICATION option.

If constraints in your database have the NOT FOR REPLICATION option, drop the constraints, and then re-create them without the NOT FOR REPLICATION option. Then, re-run the Snapshot Agent. -or-
Select the Enforce relationship for replication check box that is located under the Relationships tab of the Table Design properties page (for each constraint) in the SQL Server Enterprise Manager, and then re-run the Snapshot Agent.

MORE INFORMATION

Steps To Reproduce Behavior

Run the following SQL statements to create the tables and relationships on a SQL Server 2000 database:

CREATE TABLE "Table1" (Col1 Int Identity(1,1) PRIMARY KEY, id1 int);

CREATE TABLE "Table2" (Id2 INT Identity(1,1) PRIMARY KEY, col2 Int);

ALTER TABLE "table1" ADD CONSTRAINT "FK_table1_table2" FOREIGN KEY 

("id1") REFERENCES "table2" ("id2") NOT FOR REPLICATION;

Create a publication that includes both tables.
Subscribe through SQL Server CE and attempt to download the replica by using a sample application. The following error message occurs
RESULT: NUMBER = 80040E14
NATIVE_ERROR = 25501, SSCE_M_QP_BADPARSE