Problem Applying Schema Changes to Existing Merge Publication

Tech-Archive recommends: Speed Up your PC by fixing your registry


Publisher:
- Windows 2000 SP4
- SQL Server 2005

Subscriber:
- Tablet XP SP2
- SQLExpress SP1
- Merge Pull subscriptions with parameterized filters

All,

As brief as I can:

We have 2 table articles and a trigger in our publication. The 2
tables, Table A and Table B, have identical schema. When a new row is
inserted into Table A the trigger fires and echoes the insert into
Table B. {Table B retains a copy of the the original state of the
data for HIPPA compliance}.

We have referential integrity constraints on the two tables so Table B
has a foreign key on the primary key in Table A -- and Table A is
always processed first during replication.

Problem: We have added a new column to the schema of Table A {and
Table B} and modified the trigger definition to insert the new column
into Table B.

When we generate a new snapshot and re-initialize our subsciptions we
get an error when updating the trigger definition during the
processing of Table A because the new column has not yet been added to
Table B.

The replication attempt errors out with "column does not exist in
Table B" and then the snapshot agent stops immediately - with a half
updated/broken subscription database.

Dropping and recreating the subscription & subscription database works
- but eegaddd that is not a good option. Does anybody see a way we
can make this schema change succeed without dropping and re-creating
the publication and all subscriptions?

Is there some way we can force the replication to apply table schema
changes before it applies trigger definition changes?

Can we somehow make the snapshot agent ignore the error, continue
through to completion, and then we can modify the trigger in the post
snapshot script?

Any thoughts?

--Richard

.

Relevant Pages

  • Re: closer, but not quite
    ... the UDT is not mentioned when the snapshot agent is analyzing dependencies. ... I tried running the script on their end, ... the subscription type having problems is Push. ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscription problem BTS 2006
    ... I do have an XmlDisassembler in my custom pipeline and it's Documents ... I also have an XML validator with the same schema. ... The orchestration will be subscribing to the MessageType - this is taken ... You can add additional filters to the subscription by setting filters on ...
    (microsoft.public.biztalk.general)
  • Re: Subscription problem BTS 2006
    ... Have you tried not specifying the schema on the XmlDisassembler. ... I do have an XmlDisassembler in my custom pipeline and it's Documents ... The orchestration will be subscribing to the MessageType - this is ... You can add additional filters to the subscription by setting filters ...
    (microsoft.public.biztalk.general)
  • RE: Message Subscription
    ... Have you used the Subscription Viewer in the SDK to check and see what ... What message type did you define in your Orchestration Port? ... Note they're not our schema and we must be able to use ...
    (microsoft.public.biztalk.general)
  • Re: Subscription problem BTS 2006
    ... Do you have "Routing enabled for failed messages" set on the receive port ... Have you tried not specifying the schema on the XmlDisassembler. ... The orchestration will be subscribing to the MessageType - this is taken ... You can add additional filters to the subscription by setting filters on ...
    (microsoft.public.biztalk.general)