Could not find stored procedure 'sp_MSins_XXX' - SQL Server 2000

[John Brown]

Hello All,

First of all, I am using SQL Server 2000. Hopefully, it won't be for much
longer.

Using SQL Trace, I discovered that the distribution agent was failing
because it was calling sp_MSins_XXX;2. This procedure does not exist in my
subscriber database. I created a custom set of procedures for a lot of
tables, and because I did not see the need for grouped procedures, I created
only one, without the suffix.

So far, I am replicating three servers, and they call 'sp_MSIns_XXX' and
transactions are being replicated. I cannot find the difference between the
publications and/or the servers where it works, and this server/publication
that does not.

1) What property do I need to set to make the distribution agent call the
procedure without the suffix?

2) How does the distribution agent know the names of the procedures? Is
there a table that I could edit?

Regards,
Alias John Brown.

[Paul Ibison]

I'd take a look at the sysarticles table on this subscriber - this is where
you'll see the procedure names.
If possible, I'd drop the subscription and run sp_removedbreplication on
this subscriber database and then initialize it again.
HTH,
Paul Ibison

[John Brown]

"Paul Ibison" wrote:

Sorry to get back to you so late. I was having some space problems. The
sp_removedbreplication removed a bogus subscription from the subscriber's
list of subscriptions. I did experience the problem after that. However, it
is working now, although I don't know what I did differently. I can replicate
one table. I assume that this means that I will be able to replicate the
entire database when I get some extra space.

I would still like to know what makes replication add the ;2. In order to
generate my scripts, I selected the "create procedures when snapshot is
applied" option, and then edited the .sch scripts. I noticed that those files
contained "proc" and "proc;2", but I did not understand the significance of
that.