no check constraint causing initial synch to fail

Discussion in 'microsoft.public.sqlserver.ce' started by Rick Martin, Jul 10, 2003.

  1. Rick Martin

    Rick Martin Guest

    Hi,

    When I include a specific table in my publication I cannot sync (merge
    replication) on the CE unit. I've traced it down to the constraint listed
    below. If I remove this constraint everything works fine. The odd thing is
    that this is the only table with constraint code generated in the .DRI files
    in the snapshot folder. However, virtually all of my tables have constraints
    of the same nature.

    Two questions:
    Why does this table generate constraint code in the publication snapshot?
    Why does this constraint cause the initial synch to fail on the Pocket PC
    2002 unit?

    This is the script cut from the .DRI file in the snapshot.

    ALTER TABLE [dbo].[ChangeOrders] ADD
    CONSTRAINT [FK_ChangeOrders_Project] FOREIGN KEY
    (
    [ProjectUnique]
    ) REFERENCES [dbo].[Project] (
    [PRJUnique]
    )
    GO

    alter table [dbo].[ChangeOrders] nocheck constraint
    [FK_ChangeOrders_Project]
    GO

    Is it possible there is a timing sequence problem? If the project table
    wasn't created before this constraint was added, there wouldn't be a unique
    index on the PRJUnique field? JAT.

    TIA,
     
    Rick Martin, Jul 10, 2003
    #1
    1. Advertisements

  2. Hi Rick,

    I will give this a look. Sounds like there might be a parsing issue. What is
    the error you receive when the sync fails?
     
    Brad Syputa - MS, Jul 10, 2003
    #2
    1. Advertisements

  3. Hi Rick,

    I ran into the same problem with NoCheck. This looks like a replication
    having an issue passing on that parameter.

    SQLCE would actually not consume the NoCheck even if it was on the table.
    Check constraints, computed columns and a few other things are parsed out
    for SQLCE replicated tables.

    Check the SQLCE Books on Line for all things that are parsed.

    If you cannot get around this, let us know.
     
    Brad Syputa - MS, Jul 10, 2003
    #3
  4. Hi Rick,

    Correct. SQLCE has no support for Check Constraints. This is taken from the
    SQLCE Books On Line. Doing a search for Check Constraints found this page.

    a.. Computed columns
    Not supported. Data stored as computed columns is vertically partitioned out
    of all SQL Server CE subscriptions.

    a.. Information that is not propagated to the SQL Server CE Subscriber
    You can include the following items in a SQL Server publication, but they
    are not propagated to the SQL Server CE Subscriber:

    a.. CHECK constraints
    b.. DEFAULT definitions for columns
    c.. Extended properties
    d.. Stored procedures
    e.. Views
    f.. User-defined functions
    g.. Triggers
    Because SQL Server CE replication cannot propagate these items, you should
    implement equivalent logic in a SQL Server CE-based application. Doing so
    ensures that the SQL Server CE database remains consistent with the SQL
    Server database. For example, if the SQL Server database includes a CHECK
    constraint, the SQL Server CE-based application should implement the
    corresponding check in application code.
     
    Brad Syputa - MS, Jul 14, 2003
    #4
  5. Rick Martin

    Rick Martin Guest

    Hi Brad,

    Would it be possible to have the CE application create the missing stored
    procedures or triggers on the CE side after the subscription and database
    was created? I've already successfully added tables on the CE side that are
    not part of the replication. What would happen if I altered a table or
    added a trigger to a table that was part of the subscription?

    Thanks,
    Rick Martin
    Sharpe Software, Inc.

    rights.
     
    Rick Martin, Jul 14, 2003
    #5
  6. There is not a lot you can do on the SQLCE side. Triggers are not supported,
    and there is not much you can alter on the table. If you can control some of
    the logic in your application, as in what a user enters in a text box, you
    should do it that way.

    However, you can apply all of this logic to your SQL Server. You can create
    triggers and stored procedures that will look at the data that is being
    merged and manage the data what ever you deem necessary.

    What you have requested is something I think we will see in future versions
    of SQLCE.
     
    Brad Syputa - MS, Jul 15, 2003
    #6
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.