May 4, 2004 at 8:10 am
Hi there,
I'm trying to script out a process for replication. I've been able to get everything to work the way I want it if I go thru EM but now I want to get it to work thru an ASP page. Ok, so I have my script which is pretty much from 'generate script' in EM:
-- Dropping the merge subscription
exec sp_dropmergesubscription @publication = N'REPLTEST_DYNAMIC',
@subscriber = N'DUNGEON',@subscriber_db = N'REPLTEST_FIELD',@subscription_type = N'pull'
go
-- Dropping the merge publication
exec sp_dropmergepublication @publication = N'REPLTEST_DYNAMIC'
go
-- Adding the merge publication
exec sp_addmergepublication @publication = N'REPLTEST_DYNAMIC',
@description = N'Merge publication of REPLTEST database from Publisher CATAPULT.',
@retention = 14, @sync_mode = N'native', @allow_push = N'true', @allow_pull = N'true',
@allow_anonymous = N'false', @enabled_for_internet = N'false', @centralized_conflicts = N'true',
@dynamic_filters = N'true', @snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@conflict_retention = 14, @keep_partition_changes = N'true',
@allow_subscription_copy = N'false', @allow_synctoalternate = N'false',
@add_to_active_directory = N'false', @max_concurrent_merge = 0,
@max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @publication = N'REPLTEST_DYNAMIC',@frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 0, @frequency_recurrence_factor = 1,
@frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0,
@active_end_date = 99991231, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959
GO
-- Adding the merge articles
exec sp_addmergearticle @publication = N'REPLTEST_DYNAMIC', @article = N'SpecialFeatureStorage', @source_owner = N'dbo', @source_object = N'SpecialFeatureStorage',
@type = N'table', @description = null, @column_tracking = N'true', @pre_creation_cmd = N'drop',
@creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = N'intMasterAcctID in (17,18,19)', @vertical_partition = N'false',
@destination_owner = N'dbo', @auto_identity_range = N'false', @verify_resolver_signature = 0,
@allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0
GO
.
.
-- Adding the article subset filter
exec sp_addmergefilter @publication = N'REPLTEST_DYNAMIC', @article = N'OtherFeatures', @filtername = N'OtherFeatures_BuildingDescriptions', @join_articlename = N'BuildingDescriptions', @join_filterclause = N'BuildingDescriptions.intID=OtherFeatures.intBuildingID', @join_unique_key = 0
GO
It does everything I want it to do EXCEPT start the snapshot agent. I thought that if I added @snapshot_job_name = N'<some name here>' to the addpublication_snapshot that I could then simply do a exec sp_job_start <foo> to get it to kick off but the name is incremental, and it also doesn't seem to return anything useful (like the JobID field).
So, how the heck can I get this snapshot agent to start?
Thanks again,
Chris
May 4, 2004 at 12:24 pm
Maybe you cvan do smothing like this:
DECLARE @JobID AS UNIQUEIDENTIFIER
SELECT @JobID = job_id
FROM distribution.dbo.MSSnapshot_Agents
WHERE Publication = 'PublicationNameHere'
EXEC msdb.dbo.sp_Start_job @job_id = @JobID
May 5, 2004 at 9:05 am
Actually I ended up doing this before I saw your reply:
Declare @JobID Binary(16)
Select @JobID = job_id from msdb.dbo.sysjobs where name like '%DYNAMIC%'
exec msdb.dbo.sp_start_job @job_id = @JobID
My next problem is how can I tell when the snapshot has finished running... I'm trying to do this from an ASP application so while I realize there is going to be some wait time while things happen, I need to poll its completion so I can move on to the next step.
Thanks,
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply