Scripting database replication

I need to run a script with an alter table on a database with a transaction publication. The replication won't let this happen and spits out…

Error

#+NAME Error from running an alter

Msg 21814, Level 16, State 1, Procedure sp_MStran_autoproc, Line 182
DDL replication failed to refresh custom procedures, please run "exec sp_register_custom_scripting 'CUSTOM_SCRIPT', your_script, 'REPLICATION_NAME', 'TABLE_NAME' "and try again
Msg 3609, Level 16, State 2, Line 6
The transaction ended in the trigger. The batch has been aborted.

Solution

Just remove the subscription and run the following. (this can be scriped also).

For this to work you must be logged into the publisher and a member of sysadmin. Otherwise you need to set the job_login/job_password data.

Anything after the space in the params is &optional.

#+NAME Create a subscription

DECLARE @publisher_db sysname;
DECLARE @publication sysname;
DECLARE @subscriber sysname;
DECLARE @destination_db VARCHAR(MAX);
DECLARE @subscriber_login VARCHAR(MAX);
DECLARE @subscriber_password VARCHAR(MAX);

SET @publication = N'publication_name';
SET @subscriber = N'subscriber-server';
SET @destination_db = N'subscriber-database-name';
SET @subscriber_login = N'subscriber-login';
SET @subscriber_password = N'subscriber-password';


USE ['publisher-database']
EXEC sp_addsubscription
    @publication = @publication,
    @subscriber = @subscriber,
    @destination_db = @destintaion_db,

    @subscription_type = N'Push',
    @sync_type = N'automatic',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0

EXEC sp_addpushsubscription_agent
    @publication = @publication,
    @subscriber = @subscriber,
    @subscriber_db = @subscriber_db,
    @job_login = null,
    @job_password = null,
    @subscriber_security_mode = 0,
    @subscriber_login = @subscriber_login,
    @subscriber_password = @subscriber_password,

    @frequency_type = 64,
    @frequency_interval = 0,
    @frequency_relative_interval = 0,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 0,
    @frequency_subday_interval = 0,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 20140213,
    @active_end_date = 99991231,
    @enabled_for_syncmgr = N'False',
    @dts_package_location = N'Distributor'
GO