Merge Replication is Broken

I have scanned this newsgroup for similar postings, but came up empty.
My problem is this: I had set up merge replication between SQL Server
2000 (publisher) and several MSDE SP3 subscribers. This was working
until a week ago, but now nothing replicates.
Symptoms:
The merge agents in EM all say they are merging successfully with a
status of "No data needed to be merged". The problem is that there
is plenty of data that has changed since the last replication and none
of it is merging. I tried forcing replication to occur by starting the
snapshot agents. The merge agents are all scheduled to run every 5
minutes.
What Might Have Happened:
In the Replication\Subscriptions folder for my server, there is a
subscription that shouldn't be there. It is a Transactional
subscription. The subscriber and publisher are the same server, which
shouldn't happen. It has an error message of "Cannot drop the
table 'LocateType' because it is being used for replication".
I have done everything I can think of to remove this subscription. It
does not show up in any of the agents folders, and I have even removed
the server from the enabled subscribers list and rebooted the server.
However I still have this ghost subscription and replication still
fails to merge any changes.
Any help is greatly appreciated.
try issuing sp_MSdummyupdate to see if this generates data flow.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MillsPerry" <mperry@.wpsr.com> wrote in message
news:1117634332.031233.320780@.g14g2000cwa.googlegr oups.com...
> I have scanned this newsgroup for similar postings, but came up empty.
> My problem is this: I had set up merge replication between SQL Server
> 2000 (publisher) and several MSDE SP3 subscribers. This was working
> until a week ago, but now nothing replicates.
> Symptoms:
> The merge agents in EM all say they are merging successfully with a
> status of "No data needed to be merged". The problem is that there
> is plenty of data that has changed since the last replication and none
> of it is merging. I tried forcing replication to occur by starting the
> snapshot agents. The merge agents are all scheduled to run every 5
> minutes.
> What Might Have Happened:
> In the Replication\Subscriptions folder for my server, there is a
> subscription that shouldn't be there. It is a Transactional
> subscription. The subscriber and publisher are the same server, which
> shouldn't happen. It has an error message of "Cannot drop the
> table 'LocateType' because it is being used for replication".
> I have done everything I can think of to remove this subscription. It
> does not show up in any of the agents folders, and I have even removed
> the server from the enabled subscribers list and rebooted the server.
> However I still have this ghost subscription and replication still
> fails to merge any changes.
> Any help is greatly appreciated.
>
|||Hi Hilary,
A search of the SQL Server BOL did not find any references to this
procedure. Could you give me a brief rundown on what parameters it
expects?
I tried guessing on the settings for @.rowguid and @.tablenick without
success. Is rowguid a guid associated with a record on the table I am
trying to replicate? If so then does it matter which record? And is
tablenick the name of that table or is it something else?
use OCTAVE
go
exec sp_MSdummyupdate @.rowguid='DDDCAB2C-5C0B-4AFD-9811-064967C4036B',
@.tablenick='TicketInfo'
Thanks!
|||sorry wrong proc
try
http://msdn.microsoft.com/library/de...repl3_7r6t.asp
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"MillsPerry" <mperry@.wpsr.com> wrote in message
news:1117653529.794501.20690@.o13g2000cwo.googlegro ups.com...
> Hi Hilary,
> A search of the SQL Server BOL did not find any references to this
> procedure. Could you give me a brief rundown on what parameters it
> expects?
> I tried guessing on the settings for @.rowguid and @.tablenick without
> success. Is rowguid a guid associated with a record on the table I am
> trying to replicate? If so then does it matter which record? And is
> tablenick the name of that table or is it something else?
> use OCTAVE
> go
> exec sp_MSdummyupdate @.rowguid='DDDCAB2C-5C0B-4AFD-9811-064967C4036B',
> @.tablenick='TicketInfo'
> Thanks!
>
|||Hilary,

>From the documentation, I assume that parameter @.source_object is the
name of the table I am trying to replicate, and @.rowguid is the rowguid
of the record I am trying to replicate. I ran this script:
use OCTAVE
go
exec sp_mergedummyupdate @.source_object='dbo.TicketInfo',
@.rowguid='DDDCAB2C-5C0B-4AFD-9811-064967C4036B'
And got this result back:
Server: Msg 20003, Level 11, State 1, Procedure sp_mergedummyupdate,
Line 24
Could not generate nickname for 'TicketInfo'.
Needless to say, nothing replicated.
|||Hilary,
After sp_mergedummyupdate failed for not finding table 'TicketInfo's
nickname, I did a little more digging.
The nickname is retrieved through sp_MStablenickname, which reads
tables sysmergearticles and sysobjects.
I looked at sysmergearticles and found it empty when there should have
been two articles in it. I then checked the publication properties and
found that my articles were all dropped. After recreating them and
regenerating the snapshots, replication resumed.
Thanks for your help!