Microsoft KB Archive/300597

= BUG: NOT FOR REPLICATION clause causes SQL Server CE replication to fail =

Article ID: 300597

Article Last Modified on 2/12/2007

-

APPLIES TO


 * Microsoft Encarta Interactive World Atlas 2001
 * Microsoft SQL Server 2000 Windows CE Edition 2.0

-



This article was previously published under Q300597



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):

&quot;The database is in an unusable state. Delete it and recreate it.&quot;

28560 (SSCE_M_EXECUTEFAILED):

&quot;OLE DB Execute Method failed; bad or invalid SQL statement&quot;



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.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps To Reproduce Behavior
  Run the following SQL statements to create the tables and relationships on a SQL Server 2000 database: CREATE TABLE &quot;Table1&quot; (Col1 Int Identity(1,1) PRIMARY KEY, id1 int);

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

ALTER TABLE &quot;table1&quot; ADD CONSTRAINT &quot;FK_table1_table2&quot; FOREIGN KEY

(&quot;id1&quot;) REFERENCES &quot;table2&quot; (&quot;id2&quot;) 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



