sql server replication: Why @logreader_job_name is ignored? -- sql server replication

Why @logreader_job_name is ignored?

tohas NO[at]SPAM freenet.de
8/5/2004 8:38:42 PM

Hi NG

I setup a transact replication (mssql2k sp3a) via TSQL Code from VB6.
with the code snipped:

SQL = "exec sp_addpublication"
...
SQL = SQL & "@logreader_job_name = N'Logreader-" & Me.Server & "-" &
Me.DB & "'"

It works fine, but the name for the Logreader will ignored.

The @logreader_job_name is for internal use,
but if I look in the TSQL of
sp_addpublication procedure

there is inside a call to

sp_MSadd_logreader_agent

with the name of the logreader as parameter @name.

Is there a way to get the wished jobname without
manipulation in the
MSSQL Repl Stored Procedures?

Re: Why @logreader_job_name is ignored?

tohas NO[at]SPAM freenet.de
8/5/2004 9:46:45 PM

I have used the debugger:

sp_addpublication

calls
sp_MSadd_logreader_agent

EXECUTE @retcode = @distproc
@name = @logreader_job_name,
....

the parameter @logreader_job_name contains
the right servername.

in
sp_MSadd_logreader_agent

if not exists (select * from msdb..sysjobs_view where
name = @name and
UPPER(originating_server) =
UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName'))))
begin
set @job_existing = 0
set @name = null
end

the servername (@name)
will be to NULL

but thats not OK

there isn't a job with the same name.

Any explanations?

Re: Why @logreader_job_name is ignored?

ChrisR
8/6/2004 8:07:50 AM

select * from sysjobs j
inner join syscategories c on j.category_id = c.category_id
where c.name = 'repl-logreader'

[quoted text, click to view]

Re: Why @logreader_job_name is ignored?

Hilary Cotter
8/6/2004 12:33:16 PM

May I ask why this is important to you?

I ran into similar problems getting the name of the distribution and
snapshot agent outputted. But I can't think of why someone might want the
name of the log reader agent?

The log reader agent name also have a subtlety. You can only have one log
reader agent per database, so SQL Server may have a very good reason for
ignoring subsequent attempts to name a log reader agent. However you can't
even name your first log reader agent in a database.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]

Re: Why @logreader_job_name is ignored?

Hilary Cotter
8/10/2004 2:10:31 PM

Perhaps the SQLDMO library, DistributionPublication class,
EnumLogReaderAgentView will give you what you are looking for.

I'll try to code this tonight for you.

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]

Re: Why @logreader_job_name is ignored?

tohas NO[at]SPAM freenet.de
8/10/2004 5:29:39 PM

On Fri, 6 Aug 2004 12:33:16 -0400, "Hilary Cotter" <hilaryk@att.net>
[quoted text, click to view]

I'have written in 1997 a VB-GUI for Setup and Control a
Multimasterreplication with multiple Servers + Check and Datacompare
for MSSQL6.5-DBs. In 6.5 the given Taskname was not ignored.
Now I make a Port to MSSQL2k.
If I alter a task (schedule) in the GUI, I need the name of the Task.
Every Server replicates more than one DB, thats why there are more
than one logreader. If I want to alter a task, now I must manually
choose the name of the task with the logreader. Thats uncomfortable.

[quoted text, click to view]

Re: Why @logreader_job_name is ignored?

Hilary Cotter
8/12/2004 4:00:14 PM

try something like this - this is in vbscript

Const SQLDMOPublication_Transactional = 0
SET objServer = CreateObject("SQLDMO.SQLServer")
objServer.LoginSecure=True
objServer.Connect "Publisher"
SET objReplication = objServer.Replication
Set objDistributor=objReplication.Distributor
for each objDistributionPublisher in objDistributor.DistributionPublishers
for each objDistributionPublication in
objDistributionPublisher.DistributionPublications
if
objDistributionPublication.PublicationType=SQLDMOPublication_Transactional
then
wscript.echo objDistributionPublication.Name
wscript.echo objDistributionPublication.LogReaderAgent
end if
next
next

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

[quoted text, click to view]

Re: Why @logreader_job_name is ignored?

tohas NO[at]SPAM freenet.de
8/16/2004 1:06:12 PM

Thanks, your code works.

But it was my mistake. I needed also the name of distribution task:

I try this (clear it works only in 2k or in mssql7):

Select msdb..sysjobs.job_id, msdb..sysjobs.name
From msdb..sysjobs, msdb..syscategories
Where
msdb..sysjobs.category_id = msdb..syscategories.category_id
And msdb..syscategories.name = 'REPL-Distribution'

A job name with
PubServerName + '-' + PubDB + '-' + SubscrServerName +'-' + anyNumber
is the right job. Any number is a sequenz.

Perhaps it is possible with DMO, but I am faster to figure out wit
TSQL :-).

Regards

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.