December 10, 2013 at 2:02 pm
Hello everybody:
I have a transactional replication process going on. Want to add 1 more article to the replication process, need to do thru script (desired). Not GUI
Here is the Script:
use [UATPCDB201]
exec sp_addarticle @publication = N'UATPCDB201_PUB', @article = N'pc_activity',@source_owner = N'dbo', @source_object = N'pc_activity', @type = N'logbased',@description = N'', @creation_script = N'', @pre_creation_cmd = N'drop',@schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none',@destination_table = N'pc_activity', @destination_owner = N'dbo',@status = 24, @vertical_partition = N'false', @force_invalidate_snapshot = 1,@ins_cmd = N'CALL [dbo].[sp_MSins_dbopc_activity]', @del_cmd = N'CALL [dbo].[sp_MSdel_dbopc_activity]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dbopc_activity]'
GO
1.- I did stopped the Logreader job, run script, start snapshot job, start logreader job.
Snapshot result: 2013-12-10 20:38:36.80 [0%] A snapshot was not generated because no subscriptions needed initialization.
DOES NOT WORK.
2.- If I add the article thru the GUI SSMS, Go to Publication, properties, articles, add 1 article, click OK. Run snapshot job only, and it works.
Question:
Why it works if I go thru GUI, and it does not work with Script?. what I am missing?.
Please help.....
Need scripts to deploy the addition of articles to the Production replication process.
Thanks
Pablo Campanini
December 11, 2013 at 12:58 am
You need to add a subscription on the subscriber before running the snapshot agent.
eg
EXEC sp_addsubscription
@publication = N'<publication Name>',
@article = N'<Article Name>',
@subscriber = N'<Subscriber Name>',
@destination_db = N'<Destination Database>',
@sync_type = N'automatic',
@update_mode = N'read only',
@offloadagent = 0,
@dts_package_location = N'distributor'
December 11, 2013 at 3:28 am
Andrew G (12/11/2013)
You need to add a subscription on the subscriber before running the snapshot agent.eg
EXEC sp_addsubscription@publication = N'<publication Name>',
@article = N'<Article Name>',
@subscriber = N'<Subscriber Name>',
@destination_db = N'<Destination Database>',
@sync_type = N'automatic',
@update_mode = N'read only',
@offloadagent = 0,
@dts_package_location = N'distributor'
Actually, you need to run this on the publisher only.
What the OP has done is added the article to the publication. Theystill need to add a subscription to the article for the snapshot to be generated.
Every new article requires sp_addarticle AND sp_addsubscription. Assuming you want all new articles to go to the subscriber you only need to run ap_addsubscription once regardless of how many new articles you add to a publication. Just use @article='all'. You can add them individually but in most cases you always want all new articles to go.
December 14, 2013 at 6:02 pm
December 16, 2013 at 2:37 am
ui ranejeb (12/14/2013)
Here it is a sample:http://sqlhint.com/sqlserver/scripts/tsql/programatically-create-transactional-replication-using
Be careful with this example. It includes far more than you need to add an article to an existing publication.
You only need sp_addarticle and sp_addsubscription.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply