Schema changes screw up transactional replication

Hi

We have two servers with SQL Server 2005, version 9.0.3042 on both.

There is a transactional replication between them but when we make
schema changes
on the publisher something goes wrong. We get this error message

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

It also gives the query that is causing this

select * from MSsubscription_articlecolumns where artid= 4 and
agent_id=(select id from MSsubscription_agents where update_mode > 0
and UPPER(publisher)=UPPER(N'PublisherServerName') and publisher_db=
N'PublishedDatabase' and publication= N'PublicationName')

When we run the subquery on the subscriber

select * from MSsubscription_agents where update_mode > 0 and
UPPER(publisher)=UPPER(N'PublisherServerName') and publisher_db=
N'PublishedDatabase' and publication= N'PublicationName'

we get two rows in table MSsubscription_agents where the one with the
highest id seems to cause the error.
When we delete that row the replication starts to work again.

Does someone know why this is happening and how we can prevent it?

Regards
/Kristoffer

It looks like you are getting this error as there appears to be two
subscriptions on the same subscriber database. Are both of these
subscriptions valid or is one a rogue entry?

On Jan 16, 5:48 am, kristoffer.vi... DeleteThis @gmail.com wrote:
> Hi
>
> We have two servers with SQL Server 2005, version 9.0.3042 on both.
>
> There is a transactional replication between them but when we make
> schema changes
> on the publisher something goes wrong. We get this error message
>
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
>
> It also gives the query that is causing this
>
> select * from MSsubscription_articlecolumns where artid= 4 and
> agent_id=(select id from MSsubscription_agents where update_mode > 0
> and UPPER(publisher)=UPPER(N'PublisherServerName') and publisher_db=
> N'PublishedDatabase' and publication= N'PublicationName')
>
> When we run the subquery on the subscriber
>
> select * from MSsubscription_agents where update_mode > 0 and
> UPPER(publisher)=UPPER(N'PublisherServerName') and publisher_db=
> N'PublishedDatabase' and publication= N'PublicationName'
>
> we get two rows in table MSsubscription_agents where the one with the
> highest id seems to cause the error.
> When we delete that row the replication starts to work again.
>
> Does someone know why this is happening and how we can prevent it?
>
> Regards
> /Kristoffer