Production error: "The schema script [...] could not be propagated to the subscriber"

Hi,
A member of my team recently made a few minor changes to our database.
Several (not all) subscribers are now failing to replicate with the
following error:
The schema script 'exec sp_repldropcolumn
'[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
A column was added to or dropped from the replicated table.
(Source: l07052 (Agent); Error number: 27160)
------
' because it is a primary key column.
(Source: l07052 (Data source); Error number: 21264)
------
This was a problem a few months ago but seemed to have been resolved.
In particular, nothing at all has changed recently in the
IP_Application_Updates table. I'm very happy for that table to remain
exactly as it is.
Can anyone suggest a way to fix this? I've got a bunch of users who
are unable to replicate their database and they'll turn nasty shortly.
In the absence of an elegant fix I'll cheerfully go with a way to
change the offending script to something innocuous, but I don't know
where or why the script exists or why the subscribers are trying to run
it. I will be very grateful if anyone can help me to get my
subscribers past this error.
Many thanks
Alex
Whenever the Merge Agent runs, it first makes sure that the schema [any
change as a result of DDL] is in sync, before the
data could be replicated. All the schema changes in the publisher are stored
in sysmergeschemachange table on the
publisher database. You can take a look at this table and see the schema
changes that have been executed at the publisher.
I believe the Merge Agent is trying to drop the column at the subscriber,
but unable to do it because it is the primary key column.
One work around would be to remove the primary key constraint at the
subscriber, if you don't need that column any more.
<WombatDeath@.gmail.com> wrote in message
news:1169249897.423772.218970@.l53g2000cwa.googlegr oups.com...
> Hi,
> A member of my team recently made a few minor changes to our database.
> Several (not all) subscribers are now failing to replicate with the
> following error:
> The schema script 'exec sp_repldropcolumn
> '[dbo].[IP_Application_Updates]', 'update_id', 1' could not be
> propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ------
> A column was added to or dropped from the replicated table.
> (Source: l07052 (Agent); Error number: 27160)
> ------
> ' because it is a primary key column.
> (Source: l07052 (Data source); Error number: 21264)
> ------
> This was a problem a few months ago but seemed to have been resolved.
> In particular, nothing at all has changed recently in the
> IP_Application_Updates table. I'm very happy for that table to remain
> exactly as it is.
> Can anyone suggest a way to fix this? I've got a bunch of users who
> are unable to replicate their database and they'll turn nasty shortly.
> In the absence of an elegant fix I'll cheerfully go with a way to
> change the offending script to something innocuous, but I don't know
> where or why the script exists or why the subscribers are trying to run
> it. I will be very grateful if anyone can help me to get my
> subscribers past this error.
> Many thanks
> Alex
>
|||Hi Gayathri
Thank you for replying so quickly. The problem I have is that the
table in question is in use, and the column in question should be the
PK of that table.
We have a scheduled job which recreates the snapshot once a week, on
Saturday. I've just taken a look and most tables have three records in
sysmergeschemachange, but the IP_Application_Updates table has an
additional four:
exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
alter table [dbo].[IP_Application_Updates] drop column update_id
exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
IDENTITY (1, 1) NOT NULL], '%', 1
alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
(1, 1) NOT NULL
This is presumably what's causing the problem. I could perhaps "fix"
it by just deleting those four records from the system table but I can
just imagine causing all manner of havoc with my ignorant tinkering.
Assuming that nobody in my team has had any reason whatsoever to make
any alterations to this table in recent months, is it possible to
hazard a guess as to why the merge agent is attempting to recreate the
update_id column? And is there any way to make it stop?
Thanks
Alex
|||Hi,
The only reason I see a DDL row in 'sysmergeschemachange' table is because
of a user action after
the intial snapshot has been taken.
Trying to delete the rows from the system table could pop up some more
issues.
You could do two things:
1. drop the primary key constraint at the failing subscriber. This should
probably allow
the merge agent to complete succesfully [if this is the only problem].
2. Else, you might have to re-initalize the failing subscribers ...
Hope this helps.
-- Gayathri TK
<WombatDeath@.gmail.com> wrote in message
news:1169255444.925914.213970@.11g2000cwr.googlegro ups.com...
> Hi Gayathri
> Thank you for replying so quickly. The problem I have is that the
> table in question is in use, and the column in question should be the
> PK of that table.
> We have a scheduled job which recreates the snapshot once a week, on
> Saturday. I've just taken a look and most tables have three records in
> sysmergeschemachange, but the IP_Application_Updates table has an
> additional four:
> exec sp_repldropcolumn '[dbo].[IP_Application_Updates]', 'update_id', 1
> alter table [dbo].[IP_Application_Updates] drop column update_id
> exec sp_repladdcolumn '[dbo].[IP_Application_Updates]','update_id',[int
> IDENTITY (1, 1) NOT NULL], '%', 1
> alter table [dbo].[IP_Application_Updates] add update_id int IDENTITY
> (1, 1) NOT NULL
> This is presumably what's causing the problem. I could perhaps "fix"
> it by just deleting those four records from the system table but I can
> just imagine causing all manner of havoc with my ignorant tinkering.
> Assuming that nobody in my team has had any reason whatsoever to make
> any alterations to this table in recent months, is it possible to
> hazard a guess as to why the merge agent is attempting to recreate the
> update_id column? And is there any way to make it stop?
> Thanks
> Alex
>
|||Hi Gayathri
Thanks very much; we'll try dropping the PK constraint and see what
happens.
Thanks again for your help,
Alex