Replication: Sysmergearticles and Error 208
Invalid object name "dbo.sysmergearticles":
I have seen issues on Forums where people state that they get the error "invalid object name dbo.sysmergearticles when they try to replicate their subscriber with their publisher.
I had recently worked on an issue like this and decided to post my findings and this may be what others may be experiencing.
Problem:
While replicating subscribers with publisher, I get the following error:
"The process cannot enumerate the changes at subscriber " when running sp_Msenumchanges system stored procedure on subscriber. "Invalid object name "dbo.sysmergearticles"
Environment:
This is on a SQL Server 2000 environment (both publisher and subscriber).
Analysis:
At first, when I got this error, I ran a profiler trace to pinpoint what statement was causing this error. In my case, the sp_MSENUMCHANGES stored procedure was failing with error 208 - Invalid object name sysmergearticles.
Looking at the profiler trace, I also see that this SP is executed on the master database so I run the stored procedure manually on the master database. This generates the same error.
Now, SQL knows that the sysmergearticles table is located on the publisher database and it should be reading data from there. After a quick check to see that all data in the metadata tables where pointing to the right information, I went to have a look at how this stored procedure is created.
This is simple to do:
Grab the latest hot fix you have installed, extract it through command like (/x) and look into the replication script.
Analyzing that script, we see that after ever creation of an internal stored procedure, the following command is executed:
(example)
exec dbo.sp_MS_marksystemobject sp_MSenumchanges
go
grant exec on dbo.sp_MSenumchanges to public
Go
This marks the objects, in this case the stored procedure, as a system object.
Once I executed this statement on my SQL server, the issue was resolved.
IMPORTANT NOTE: Internal Stored Procedures should not be altered.