Merge Replication Failed - Subscription to publication is invalid

Question

Merge Replication Failed - Subscription to publication is invalid

Vince Pacella

SSC Veteran

Points: 267
More actions
August 26, 2004 at 1:52 am

#360696

Remote computer (subscriber): SS 2000 Desktop, Mixed Mode. Name: LGBBPS
Local computer (publisher, distributor): SS 2000 Ent, Mixed Mode. Name: CHI7155
Both are on the same AD network, and SQL Agent is running under my Domain account at both.
Replication has worked for this two computers for months, but now I get "The subscription to publication 'IPP' is invalid"

The Merge agent seems to run through its entire script before this error is generated. The last script to run is "MotorbridgeContainer_327.trg".. there's no object with a 328 in the publisher folder.
At the same time I have replication working from publisher CHI7155 with 6 other remote computers with no problems.

What can I do? I searched MS' KB but nothign seems to apply.
I un/re-registered LGBBPS to EM. LGB is a subscriber on the publishers Replication settings (Subscription tab) ,and it's a push replication that is failing.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vince Pacella SSC Veteran Points: 267 More actions · Answer 1

I forgot to mention, I did run Profile on the subscriber, so I think I should have all the activity that was going on, if that helps.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

Hi Vince

Check the error details for the merge agent. It should give you the exact script which fails. When check the script. I've seen cases that a script is calling objects which no longer exist or don't exist on the subscriber. When applying the script to the supplier it will fail and the merge agent stops.

About the fact that there is no script 328, this is you might just look for the next higher number 329 or 330, that's most likely the failing script.

M

[font="Verdana"]Markus Bohse[/font]

Steve Walker Valued Member Points: 54 More actions · Answer 3

Hi - It sounds as if your snapshot is invalid. The publication has a setting called retention days, check this value & if it reads 14days (for example) it will expire after this period even though the merge agent has been running all the while.

A quick way to get a new snapshot done is: Generate a new snapshot & restore the subscription DB. When done drop the old subscription & recreate using the option - Subscriber DB allready has schema & data. The merge agent will kick in after the meta data has been loaded.

Vince Pacella SSC Veteran Points: 267 More actions · Answer 4

Markus/Steve:

I've tried both your ideas.

Markus: the specific error I'm getting is this:

The subscription to publication 'IPP' is invalid.

(Source: Merge Replication Provider (Agent); Error number: -2147201019)

---------------------------------------------------------------------------------------------------------------

The remote server is not defined as a subscription server.

(Source: MTLBPS (Data source); Error number: 14010)

---------------------------------------------------------------------------------------------------------------

This is after I removed all subscriptions from the publication.

Removed all articles from the publication.

Deleted the publication.

Rebuilt the publication and resubscribed.

The servers that worked before, still work, the servers that didnt' work before still dont.

In the above error.. it's from the Merge Agent. MTLBPS is the remote subscriber (Push).

Vince Pacella SSC Veteran Points: 267 More actions · Answer 5

KB217395 solved my problem.

My two problem servers did not have themselves registered in their own internal tables.

SYMPTOMS

When attempting a remote procedure call (RPC) from one server to another

(for example, by executing a stored procedure on a remote computer with a statement such

as EXEC SERV_REMOTE.pubs..byroyalty), you may receive the following error message:

Error 18482: Unable to connect to site '0' because '' is not defined as a remote server at the site.

CAUSE

This error occurs when SQL Server cannot execute a remote procedure call. This can be caused

by an improperly configured local server. To make a remote procedure call, SQL Server first

determines who the local server is by looking for the server name with srvid = 0 in sysservers.

If an entry with srvid = 0 is not found in sysservers, or if the server name with srvid = 0 belongs

to a server name that is different from the local Windows NT computer name, you will receive the error.

WORKAROUND

To determine if the local server is configured correctly, examine the srvstatus column in master..sysservers.

This value should be 0 for the local server.

For example, suppose your local server was named "SERV_LOCAL", the remote server was named "SERV_REMOTE",

and sysservers contained the following information:

   srvid srvstatus      srvname            srvnetname

----- ---------      ---------------    ------------    ...

1     1              SERV_LOCAL         SERV_LOCAL

2     1              SERV_REMOTE        SERV_REMOTE

In the preceding output, SERV_LOCAL is the local server, but it has a srvid of 1; it should be 0.

To correct this, follow these steps:

Run sp_dropserver local_server_name, droplogins (in this example, you would run sp_dropserver SERV_LOCAL, droplogins).
Run sp_addserver local_server_name, LOCAL (in this example, you would run sp_addserver SERV_LOCAL, LOCAL).
Stop and restart SQL Server.

After running those steps, the sysservers table should look like the following:

   srvid srvstatus      srvname            srvnetname

----- ---------      ---------------    ------------    ...

0     0              SERV_LOCAL         SERV_LOCAL

2     1              SERV_REMOTE        SERV_REMOTE

Note that server ID (srvid) should be 0 for the local server