Replication Expired subscription clean up job failed

Don't know why this job failed since I deleted and rebuilt the subscriber and
publisher last week. Can someone help?
Error detail says: 'Executed as user: FOSTER\sqlservice. Could not find
stored procedure ''. [SQLSTATE 42000] (Error 2812) Associated statement is
not prepared [SQLSTATE HY007] (Error 0). The step failed.'
Appreciate your time.
Tsuey
Tsuey,
please take a look at this to see if it applies:
http://support.microsoft.com/kb/884854
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks for the quick reply. I read this article and don't think it apply.
I don't think I created the replication as transactional replication with
queued updating. If I did, SQL Server will add a new column in each
replicated table, right? How can I sure that the replication is not
configured as queued update?
Tsuey
"Paul Ibison" wrote:

> Tsuey,
> please take a look at this to see if it applies:
> http://support.microsoft.com/kb/884854
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Tsuey,
what is the 'update mode' if you run sp_helpsubscription?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul, I tried to execute sp_helpsubscription in Query analyzer (hope this
is the right tool to run it) but how do I display the result set? I set the
result in grid or text or file, but can not get the result set to display.
It just simply tells me 'The command(s) completed successfully'. What did I
do wrong? The syntax I use is as below:
exec sp_helpsubscription
GO
"Paul Ibison" wrote:

> Tsuey,
> what is the 'update mode' if you run sp_helpsubscription?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||You'll need to execute this at the publisher on the publication database and
provide the subscriber name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul, I got the sp_helpsubscription ran successfully!! The update mode
for all rows are 0's. It means 'read-only'. What does this tell me? FYI,
we configure the replication as a push replication.
Thanks for your time,
Tsuey
"Paul Ibison" wrote:

> You'll need to execute this at the publisher on the publication database and
> provide the subscriber name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||OK - then the article definitely doesn't apply. I'd be tempted to reapply
your latest service pack on the distributor, publisher then subscriber. If
that doesn't resolve this issue they I'd raise a support case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
Thanks very much for your reply. I'll try appying the SP4 to see if it helps.
Tsuey
"Paul Ibison" wrote:

> OK - then the article definitely doesn't apply. I'd be tempted to reapply
> your latest service pack on the distributor, publisher then subscriber. If
> that doesn't resolve this issue they I'd raise a support case.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Replication distribution cleanup error

I used the SQL Server 2005 publication wizard to set up transactional replication. It scheduled a job called "Distribution clean up: distribution" that calls sp_MSdistribution_cleanup. The job fails every time it runs. The xp_cmdshell is enabled and SQL Server Agent runs under an account that has sufficient privileges to execute it. The account also has full rights to the repldata subdirectories. I have applied service pack 1.

I looked through the sp_MSdistribution_cleanup code and it looks like there are two stored procedures and one function missing from the SQL server databases. They are

master.dbo.sp_MSunc_to_drive sys.fn_escapecmdshellsymbolsremovequotes sys.sp_MSdrop_distribution_agentid

Where can I get a copy of these stored procedures? The expired distribution data is slowly consuming my remaining hard drive space. Thanks.

Joe

Hi, Joe,

There seems to be a bug in our code where we should not call master.dbo.sp_MSunc_to_drive, instead we should call sys.sp_MSunc_to_drive. In SQL Server 2005, we have move most of the system sps to mssqlsystemresource database, and some of them are hidden from end users as they're not supposed to be used directly by customers. sys.sp_MSunc_to_drive, sys.fn_escapecmdshellsymbolsremovequotes and sys.sp_MSdrop_distribution_agentid are hidden sps/function thus you don't see them but they do exist.

I will do more research and see if there is a workaround.

By the way, can you post the exact error message you're seeing from distribution cleanup job (by using replication monitor to view the job history)? Are you using dynamic snapshot merge replication? Any specific information about your replication setup would be helpful.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for answering me so quickly. The problem has been resolved. The account under which xp_cmdshell was executing did not have the proper access to the repldata share.

Joe

|||

Glad to hear that the problem is solved. The issue mentioned in my post is a minor issue and should not cause any functional problems. It would be helpful for other visitors of this thread if you can post the error message you're seeing before it's fixed (it should still be available in replication agent history).

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I'm really sorry, but the clean up agent already deleted the history. I think part of the message was "Could not clean up the distribution transaction tables."

Joe

sql

Replication Clean Up of Failed Server

On my distribution server, I have a failed replication (the big red X) for a
server that had hardware failure a couple of weeks ago. We were able to
bring this client up on a separate server and have re-replicated them. Now
I need to get rid of the failed replication however when I try to simply
delete it, it obviously cannot connect to the failed server since it doesn't
exist and therefore will not delete the information from the DISTRIBUTOR.
I would like to avoid manual system table clean up unless someone has the
method that will completely remove this information.
Is there a way to do this correctly?
Thanks,
Adam P. Cassidy
Did you follow these instructions?
http://support.microsoft.com/kb/324401
Also on your distributor you may have to issue a call to
sp_dropdistpublisher
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
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:OnM$cTvaHHA.2436@.TK2MSFTNGP06.phx.gbl...
> On my distribution server, I have a failed replication (the big red X) for
> a
> server that had hardware failure a couple of weeks ago. We were able to
> bring this client up on a separate server and have re-replicated them.
> Now
> I need to get rid of the failed replication however when I try to simply
> delete it, it obviously cannot connect to the failed server since it
> doesn't
> exist and therefore will not delete the information from the DISTRIBUTOR.
> I would like to avoid manual system table clean up unless someone has the
> method that will completely remove this information.
> Is there a way to do this correctly?
> Thanks,
>
> Adam P. Cassidy
>