January 23, 2006 at 2:35 am
Hello everybody,
I try to generate a script from a publication on SQL2K server. This worked last week for another publication, but today all I get is an error 2812 "Could not find stored procedure distribution..sp_MShelpsubscriptionjobname". I then tried it with the publication I scripted succesfully last week, but the same error.
I checked the distribution database and the procedure in question does not exists.
Next I configured a new distributor and publication on a test machine and even though the new distribution database doesn't have a procedure called sp_MShelpsubscriptionjobname, I am able to generate scripts. What is happening here ?????
Any ideas
Tia Markus
[font="Verdana"]Markus Bohse[/font]
January 23, 2006 at 3:51 am
Hello everybody,
I think I solved the issue. First of all the procedure in question resides in the master and not in the distribution database. Second the fact that I couldn't create the scripts seems to be connected to the fact that we use a remote distributor. As long as a publication has no subscriptions I can generate scripts, but as soon as a subscriptions is added the gererate script function raises this error.
Which leaves the question, how can I generate scripts which have all the information of the publications including the subscribers ?
Markus
[font="Verdana"]Markus Bohse[/font]
January 24, 2006 at 5:39 pm
I'll take a stab at it, even though a) I've never had any trouble scripting pubs with subs, and b) I can't fing that proc in any database on the distributor, the publisher, nor the subscriber.
This sounds like an issue with permissions or authentication. Are you connecting to EM using NT authentication or a SQL account? Does your login have access to the remote distributor? Replication talks to the distributor through the distributor_admin id, but I suspect that EM doesn't do the same while generating a script.
January 25, 2006 at 1:44 am
Skeane,
yes I connect with my windows account which has sysadmin permissions on the the subscriber, the distributor and the publisher. And as I wrote before the procedure sp_MShelpsubscriptionjobname can be found on the publisher in the master database.
Also the publisher and the distributor run under the same domain account. After manually adding the procedure sp_MShelpsubscriptionjobname on the distributor I get a different error message, "Error 208: Invalid objectname dbo.MSdistributionagents". This of course is a table which exists in the distribution database.
I can script any publication without subscriptions and I tried on my workstation that I can also script any replication with subscriptions as long as I use a local distributor.
Markus
[font="Verdana"]Markus Bohse[/font]
January 25, 2006 at 10:26 am
I wish I could see what you see, said the blind man. I have confirmed do not have that proc in any database on any of the three servers. Are you pushing or pulling the subscriptions? Transactional, merge or snapshot replication?
It sounds like the proc might belong in distribution, not master. I say this because the original error msg is qualified that way and the 208 error references a distribution object. If you look at the proc's code I'm sure you will find it does nothing to establish the database for the object.
January 26, 2006 at 4:30 am
Skeane
we use transactional replication with push subscriptions on SQL2K SP4. The reason why it's not working is probably that the distributor server is not defined as a linked server on the Publisher and so the procedure can't access the jobinformation. Anyhow for your information I copied the code from the procedure. I might dive into it myself and tweak it a little so it satisfies my needs,but at the moment more urgent things keep me busy.
create procedure dbo.sp_helpsubscriptionjobname
(
@publication sysname,
@subscriber sysname,
@subscriber_db sysname
)
as
begin
set nocount on
declare @publisher sysname
declare @publisher_db sysname
declare @retcode int
declare @distproc nvarchar(1000)
declare @distributor sysname
declare @distribdb sysname
declare @independent_agent int
set @publisher = @@servername
set @publisher_db = db_name()
set @retcode = 0
set @independent_agent = null
exec @retcode = sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
return 1
if object_id('dbo.syspublications') is null or
object_id('dbo.sysextendedarticlesview') is null or
object_id('dbo.syssubscriptions') is null
return 1
-- Check that the specified push subscription exists and whether it
-- uses a shared agent
select @independent_agent = pub.independent_agent
from dbo.syspublications pub
inner join dbo.sysextendedarticlesview art
on pub.pubid = art.pubid
inner join dbo.syssubscriptions sub
on sub.artid = art.artid
inner join master..sysservers srv
on sub.srvid = srv.srvid
where pub.name = @publication
and upper(srv.srvname) = upper(@subscriber)
and sub.dest_db = @subscriber_db
and sub.subscription_type = 0
if @independent_agent is null
return 1
if @independent_agent = 0
set @publication = 'ALL'
exec @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor output,
@distribdb = @distribdb output
if @retcode <> 0 or @@error <> 0
return 1
set @distproc = quotename(@distributor) + N'.' + quotename(@distribdb) + '..sp_MShelpsubscriptionjobname'
exec @retcode = @distproc
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber_db = @subscriber_db
return @retcode
end
Have fun
Markus
[font="Verdana"]Markus Bohse[/font]
September 4, 2007 at 7:08 am
This started happening on each SQL machine as we applied SP4. The standard script to script out replication will work if it's in a DTS packages, but will no longer work when a Job calls the DTS package, nor when the job has the ActiveX script in it directly.
John Scarborough
MCDBA, MCSA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply