Distribution Agent Fails with Error 2812

Question

I had an error free transactional replication running between MSSQL 2000 (pub) and MSSQL 2008 R2 (sub). Because schema changes were disabled, I needed to delete the subscription and publication to add a new column to one of the replicated tables. I recreated the publication and added the subscriber. The snapshot was recreated. Now when the distribution agent runs it fails with error 2812. The Distribution DB is on the 2008 R2 box and the subscribers are pull subscriptions.

I ran a trace on the subscriber and found that the replcmd that was failing is 'exec sp_MSins_z2t_Activity;2'. NOTE the ';2'.

I review the snapshot data and the replcmd script has CREATE PROCEDURE sp_MSins_z2t_Activity *without* ';2'. Hence the subscriber database as sp_MSins_z2t_Activity.

I deleted and recreated the pub/sub a couple of times with a *new* INS/DEL/UPD cmds and still get a ';2' when the distribution agent runs. I understand the use of the ';2', but it unnecessary.

So how can I fix this??

SteveB.

Answer 1

Hi SteveB,

I'm unsure why there is a name mismatch but try renaming the procedure sp_MSins_z2t_Activity to sp_MSins_z2t_Activity2 on the subscriber database.  This will alleviate the 2812 error and allow the distribution agent to resume.

---

Brandon Williams (blog | twitter)

Answer 2

This is normal. It just means it is the second version of the proc. What error message were you getting?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 3

It's not normal if the subscriber database didn't have a version ;1 to begin with. Since I repeated this process several times, I deleted the replcmds from the subscriber database to start fresh. Each time the trace showed a call to:

'exec sp_MSins_z2t_Activity;2'. NOTE the ';2'.

I tried to rename the replcmds but it was not recognized, it still failed with the error:

Could not find stored procedure 'sp_MSins_z2t_Activity'. (Source: MSSQLServer, Error number: 2812)

Since a semi-colon is a delimiter, it needs to be called like 'exec [sp_MSins_z2t_Activity;2]' but the replcmd doesn't use brackets.

What doesn't make sense is that I generated *new replcmds* names when I re-created the *publication* and the replcmds at the subscriber still uses a ';2' reference with the new replcmd name. Mighty strange.

 

Answer 4

SteveB,

What does this return on the publisher?

SELECT artid, dest_table, ins_cmd FROM sysarticles

The ins_cmd will need to match the proc name on teh subscriber.

---

Brandon Williams (blog | twitter)

Answer 5

It contains what you expect:

z2t_Activity CALL sp_MSins_z2t_Activity

And this stored procedure exists on the subscriber. The problem is when the *distribution agent* runs it is calling:

'exec sp_MSins_z2t_Activity;2'.

(NOTE the ';2') on the subscriber.

Answer 6

And on the subscriber, what is the object_name for the insert command in MSreplication_objects?

USE MySubDB;
GO

SELECT * FROM dbo.MSreplication_objects

---

Brandon Williams (blog | twitter)

Answer 7

The insert command is: 'p_MSins_z2t_Activit' which doesn't exist in the subscriber database.

The stored procedures that are generated and exist in the subscriber database are

sp_MSins_z2t_Activity

p_MSins_z2t_Activit_msrepl_ccs

Answer 8

Can you wrap the ins_cmd for the article in brackets at the publisher?  I noticed your CALL did not have this.  For instance, the ins_cmd for my Person.Contact article looks like this:  CALL [dbo].[sp_MSins_PersonContact]

Yours was: CALL sp_MSins_z2t_Activity

You can update this using sp_changearticle:

sp_changearticle
	@publication = 'PubName',
	@article = 'z2t_Activity',
	@property = 'ins_cmd',
	@value = 'CALL [dbo].[sp_MSins_z2t_Activity]'

---

Brandon Williams (blog | twitter)

Answer 9

After running sp_changearticle to use the brackets, now I get the same error and the trace shows:

exec [sp_MSins_z2t_Activity];2

Note the ';2' which of course that procedure doesn't exist.

Answer 10

InoYouRyder, can you do me a favor. In your publication database can you do this?

Create Proc sp_MSins_z2t_Activity;2

AS

Print 'help me'
GO

What message do you get?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 11

I find it really hard to believe that it is calling '[sp_MSins_z2t_Activity];2' when the insert command is set to '[sp_MSins_z2t_Activity]'.

You might want to contact PSS on this one.

---

Brandon Williams (blog | linkedin)

Answer 12

Another option is to script out the proc and see how many versions of it exist.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 13

Running Create Proc [sp_MSins_z2t_Activity];2 created the procedure. I scripted the others out and the ;2 versions do not exist.

Imagine my surprise when I discovered in the SQL trace a call to sp_xx;2! I normally don't have problems I can't resolve.

I really wanted MS to chime in here, I thought that is what the propose of this forum.

Answer 14

Hi INoYouRyder.

I'm just facing the same problem right now.

Can you tell me if you were able of resolving it?

By the way I have open an incident with Microsoft PSS. I'll post here some kind of solution.

Thanks in advance.