"exec sp_register_custom_scripting 'CUSTOM_SCRIPT' ERROR

[LPR-3rd]

Configuration

SQL 2008 (Server A) replicates to SQL 2008(Server B) which replicates
to SQL 2008(Server C).

I recently added a column (to Server A) to a replicated table SMS &
the DDL change replicated to Server B with out a problem. When the
DDL change replicated to Server C, I received the error below.

'DDL replication failed to refresh custom procedures, please run "exec
sp_register_custom_scripting 'CUSTOM_SCRIPT', your_script,
'publication_name_here', 'table_name_here' "and try again (Source:
MSSQLServer, Error number: 21814)'

These subscriptions (on Server B to Server C) were created via a
script below.

exec sp_addsubscription @publication = N'EDI to XLOCX', @subscriber =
N'RXLOCXS-SQLA', @destination_db = N'EDI', @subscription_type =
N'Push', @sync_type = N'replication support only', @article = N'all',
@update_mode = N'read only', @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'EDI to XLOCX
(Merge)', @subscriber = N'RXLOCXS-SQLA', @subscriber_db = N'EDI',
@job_login = N'ROUSES.COM\RXLOCXSQLREPL', @job_password =
N'XPASSWORDX', @subscriber_security_mode = 1, @frequency_type = 4,
@frequency_interval = 1, @frequency_relative_interval = 1,
@frequency_recurrence_factor = 1, @frequency_subday = 8,
@frequency_subday_interval = 1, @active_start_time_of_day =3300,
@active_end_time_of_day = 235959, @active_start_date = 20070923,
@active_end_date = 99991231, @enabled_for_syncmgr = N'False',
@dts_package_location = N'Distributor'

GO

So the million dollar question is, why do I get the error 'exec
sp_register_custom_scripting 'CUSTOM_SCRIPT', your_script' when I add
a column to a table in the EDI to XLOCX publication???

AHIA,

LarryR...

[Ben Thul]

That's a weird error, but one thing that jumps out at me is the value
for @sync_type in your call to sp_addsubscription. That value should
only be used if the publisher is at rest (that is, no changes are
being made either data-wise or schema-wise). Essentially, it tells
replication that nothing needs to be done to bring the subscriber in
sync with the publisher. This is almost never the case. So, it could
be that you're missing something at the subscriber that replication is
looking for and so it's throwing that error. This is all wild
speculation on my part.
--
Ben

[LPR-3rd]

Thanks Ben...I'll look into that!!

[LPR-3rd]

Ben,

Some more detail about this problem can be found @ the link below...

http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/866b94ce-8ff9-42a6-aa61-641f02c0d3e2/

Still no resolution.

LarryR...