Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Transactional Replication. snapshot Agent not picking up Creation Script Expand / Collapse
Posted Thursday, October 3, 2013 9:08 AM



Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 10,808, Visits: 14,834
I'm working on some custom transactional replication as part of a partial database/application upgrade. Table definitions have changed in the publisher, but not in the subscribers. I need to run a custom creation script to modify the subscriber and the distribution agent is not picking it up. Here's the article creation script:

EXEC sp_addarticle
@publication = N'WB_to_opls_character',
@article = N'Employee',
@source_owner = N'dbo',
@source_object = N'Employee',
@type = N'indexed view logbased manualboth',
@description = N'',
@creation_script = 'F:\Data\Cleanup tbl_Employee.sql',
@pre_creation_cmd = N'delete',
@schema_option = 0x00,
@identityrangemanagementoption = N'manual',
@destination_table = N'tbl_EMPLOYEE',
@destination_owner = N'dbo',
@status = 0,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_dboEmployeeCustom]',
@del_cmd = N'CALL [sp_MSdel_dboEmployeeCustom]',
@upd_cmd = N'SCALL [sp_MSupd_dboEmployeeCustom]',
@sync_object = N'vEmployee',
@sync_object_owner = N'sync'

And yes I'm jumping through some hoops here to make replication work. I was given the option of replication (which was already being used) or linked servers, synonyms, and other hoops and chose replication. SSIS may have been a better choice, but they want near real-time and once I learned I could jump through some hoops with replication to make the source look like the destination I decided to go with it.


P.S. I had it working, forgot to save the script and now for some reason the creation script is not getting picked up by the snapshot agent.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1501241
Posted Saturday, December 21, 2013 5:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 21, 2013 6:05 AM
Points: 1, Visits: 2
Did you solve the problem?
I got the same issue. Creation script is not picking up. As a result - table not created and replication failed.
Post #1525260
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse