Replication Question

Hi,
I'm getting the error below when executing:
sp_addsubscription.
Prior to this I have, added the pub with sp_addpublication followed by
sp_addpublication_snapshot followed by sp_grant_publication_access followed
by sp_addarticle.
SQLServer 2000 SP3 contains the Publication and the Distribution agent is
also on a 2000 server SP3. I have just installed a new server with SP4 which
is my intended subscriber... but I can't get past this error. I have other
replication evvironments setup and they run fine.
This is the error I'm receiving:
Server: Msg 14070, Level 16, State 1, Procedure sp_changesubstatus, Line 993
Could not update the distribution database subscription table. The
subscription status could not be changed.
Server: Msg 14057, Level 16, State 1, Procedure sp_addsubscription, Line 971
The subscription could not be created.
[OLE/DB provider returned message: Unspecified error]
Thx
Something has gone quite wrong along the line so let me start by asking you a
few questions which will hopefully shed some light on the situation:
1) Are there any memory dumps in the LOG folder of any of the SQL Server
instances involved?
2) Are the Publisher and the Distributor on the same SQL200(sp3) instance
(sounds like that is the case but I want to make sure)?
3) Has MDAC on the Publisher\Distributor been updated recently? Or, what is
the version of sqloledb.dll on the Publisher\Distributor machine?
4) Can you add a simple publication from another test database successfully?
If not, which command is it failing on?
-Raymond
"Allen" wrote:

> Hi,
> I'm getting the error below when executing:
> sp_addsubscription.
> Prior to this I have, added the pub with sp_addpublication followed by
> sp_addpublication_snapshot followed by sp_grant_publication_access followed
> by sp_addarticle.
> SQLServer 2000 SP3 contains the Publication and the Distribution agent is
> also on a 2000 server SP3. I have just installed a new server with SP4 which
> is my intended subscriber... but I can't get past this error. I have other
> replication evvironments setup and they run fine.
> This is the error I'm receiving:
> Server: Msg 14070, Level 16, State 1, Procedure sp_changesubstatus, Line 993
> Could not update the distribution database subscription table. The
> subscription status could not be changed.
> Server: Msg 14057, Level 16, State 1, Procedure sp_addsubscription, Line 971
> The subscription could not be created.
> [OLE/DB provider returned message: Unspecified error]
> Thx
>

Replication job does not end - even when its idle on the server

I am the two stored procedures below, used in a similar way, to add a
subscription. But for some reason, the replication completes but it does not
stop. If I add a subscription using Enterprise manager (EM), the job does
stop. I scripted out the subscription that EM had created and noticed that
extra code had been added to create the replication jobs (ie. if you generate
the script for a job, there is an option to "script creation of replication
jobs"). Do I need to do this too?
Any help would be very much appreciated.
ServerName...../
use NewTest
GO
exec sp_addpullsubscription @.publisher = @.@.ServerName, @.publisher_db =
'pubs',
@.publication = 'pubs', @.independent_agent = 'true',
@.subscription_type = 'anonymous', @.update_mode = 'read only',
@.immediate_sync = 1
GO
exec sp_addpullsubscription_agent @.publisher = @.@.ServerName, @.publisher_db =
'pubs', @.publication = 'pubs', @.distributor = @.@.ServerName,
@.subscriber_security_mode = 1, @.distributor_security_mode = 1, @.use_ftp =
'true', @.publication_type = 1
GO
/..... End: Script to be run at Subscriber: @.@.ServerName ...../
What does the replication job which does not stop say?
Scripting replication jobs is not necessary unless you have customized them.
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
""Julian Reed via droptable.com"" <forum@.droptable.com> wrote in message
news:50DAB174F7810@.droptable.com...
> I am the two stored procedures below, used in a similar way, to add a
> subscription. But for some reason, the replication completes but it does
not
> stop. If I add a subscription using Enterprise manager (EM), the job does
> stop. I scripted out the subscription that EM had created and noticed
that
> extra code had been added to create the replication jobs (ie. if you
generate
> the script for a job, there is an option to "script creation of
replication
> jobs"). Do I need to do this too?
> Any help would be very much appreciated.
> ServerName...../
> use NewTest
> GO
> exec sp_addpullsubscription @.publisher = @.@.ServerName, @.publisher_db =
> 'pubs',
> @.publication = 'pubs', @.independent_agent = 'true',
> @.subscription_type = 'anonymous', @.update_mode = 'read only',
> @.immediate_sync = 1
> GO
> exec sp_addpullsubscription_agent @.publisher = @.@.ServerName, @.publisher_db
=
> 'pubs', @.publication = 'pubs', @.distributor = @.@.ServerName,
> @.subscriber_security_mode = 1, @.distributor_security_mode = 1, @.use_ftp =
> 'true', @.publication_type = 1
> GO
> /..... End: Script to be run at Subscriber: @.@.ServerName ...../