SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_AddMergePushSubscription_Agent fails with DBCC error


sp_AddMergePushSubscription_Agent fails with DBCC error

Author
Message
irobertson
irobertson
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 675
Hi,

I have a SQL2005 publication that is merge replicated out to various subscribers (all SQL2005 Express/Workgroup). Recently, I have found that one of our subscribers has lost its merge distribution agent. It does not appear in the Replication Monitor list, neither does a merge agent job exist on the distribution server for this subscriber.

The subscription is still listed in sysmergesubscriptions with Status = 1 (active). A snapshot agent job exists for this subscription.

I have tried re-adding the agent by executing sp_AddMergePushSubscription_Agent with the following parameters:

EXEC dbo.sp_AddMergePushSubscription_Agent
@publication = <PublicationName>
, @subscriber = <SubscriberName>
, @subscriber_db = <DatabaseName>
, @frequency_type = 2 -- on demand



These parameters are all valid and correct.

This generates the following error:


Msg 2560, Level 16, State 9, Procedure sp_MSadd_merge_agent, Line 376
Parameter 2 is incorrect for this DBCC statement.
The statement has been terminated.
Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137
Cannot insert the value NULL into column 'name', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails.


The source of the error appears to be a call to the undocumented DBCC addinstance command that is part of sp_AddMergePushSubscription_Agent

Any suggestions for how I get this subscription back up and running? I am considering manually creating the merge agent job (by scripting out one of the other subscribers' job, see end of post), but this needs a parameter, agent_id, that I can't seem to find within the database. Although, looking at the application_name column of sysmergesubscriptions, this seems to follow a convention like this:

SERVERNAME-DBNAME-PUBLICATIONNAME-SUBSCRIBERNAME-AGENT_ID

So perhaps I could just use this?

Although, I'm not sure if this would even solve the problem as I suspect that the job might need to be registered somewhere?

Regards, Iain

Job creation script:

USE [msdb]
GO
/****** Object: Job [<Merge_Job_Name>] Script Date: 06/25/2013 14:49:53 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [REPL-Merge] Script Date: 06/25/2013 14:49:53 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'REPL-Merge' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'REPL-Merge'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'<Merge_Job_Name>',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'REPL-Merge',
@owner_login_name=N'distributor_admin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Merge Agent startup message.] Script Date: 06/25/2013 14:49:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Merge Agent startup message.',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'sp_MSadd_merge_history @perfmon_increment = 0, @agent_id = XXXX, @runstatus = 1,
@comments = ''Starting agent.''',
@server=N'<Distribution Server>',
@database_name=N'distribution',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run agent.] Script Date: 06/25/2013 14:49:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run agent.',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=10,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'Merge',
@command=N'-Publisher <Publication Server> -PublisherDB <PublicationDB> -Publication <Publication> -Subscriber <Subscriber> -SubscriberDB <PublicationDB> -Distributor [<Distribution Server>] -DistributorSecurityMode 1 -HostName <Subscriber> ',
@server=N'<Distribution Server>',
@database_name=N'distribution',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Detect nonlogged agent shutdown.] Script Date: 06/25/2013 14:49:54 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Detect nonlogged agent shutdown.',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'sp_MSdetect_nonlogged_shutdown @subsystem = ''Merge'', @agent_id = XXXX',
@server=N'<Distribution Server>',
@database_name=N'distribution',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


irobertson
irobertson
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 675
I figured updating this might help someone sometime.

Creating the replication agent job manually didn't work. The job itself could be executed, but it didn't do anything and didn't result in the subscription reappearing in the Replication Monitor list. In the end, we had to drop and recreate the subscription, then manually edit the publisher sysmergesubscriptions record from the subscriber version of this table using this script:

update b
set recgen = a.sentgen
, recguid = a.sentguid
, sentgen = a.recgen
, sentguid = a.recguid
, status = 1
from <MySubscriberServer>.<MyDBName>.dbo.sysmergesubscriptions a
join sysmergesubscriptions b
on a.pubid = b.pubid
where a.pubid like 'B%' -- only relevant for my circumstances - use suitable criteria to limit to your publication
and a.subscriber_server = '<MyPublisherServer>'
and b.subscriber_server = '<MySubscriberServer>';

update b
set schemaversion = a.schemaversion
, replicastate = a.replicastate
from <MySubscriberServer>.<MyDBName>.dbo.sysmergesubscriptions a
join sysmergesubscriptions b
on a.pubid = b.pubid
where a.pubid LIKE 'B%' -- only relevant for my circumstances - use suitable criteria to limit to your publication
and a.subscriber_server = '<MySubscriberServer>'
and b.subscriber_server = '<MySubscriberServer>';



Obviously, this relies on having your subscriber server set up as a linked server on your publishing server.

This allowed the subscription to be synchronised normally. Incidentally, this approach can also be used to unmark a subscription that has been marked for reinitialisation.

Edit: typo
Edit2: removed database name
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search