Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sp_AddMergePushSubscription_Agent fails with DBCC error Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 7:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:44 AM
Points: 227, Visits: 665
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:

Post #1467157
Posted Tuesday, July 02, 2013 11:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:44 AM
Points: 227, Visits: 665
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
Post #1469673
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse