"Crash" <sourcenexus@san.rr.com> wrote in message
news:1193767796.505096.104170@v29g2000prd.googlegroups.com...
>
> 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
>