sp_adddynamicsnapshot_job (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Creates an agent job that generates a filtered data snapshot for a publication with parameterized row filters. This stored procedure is executed at the Publisher on the publication database. This stored procedure is used by an administrator to manually create filtered data snapshot jobs for Subscribers.

Note

In order for a filtered data snapshot job to be created, a standard snapshot job for the publication must already exist.

For more information, see Create a Snapshot for a Merge Publication with Parameterized Filters.

Transact-SQL syntax conventions

Syntax

sp_adddynamicsnapshot_job
    [ @publication = ] N'publication'
    [ , [ @suser_sname = ] N'suser_sname' ]
    [ , [ @host_name = ] N'host_name' ]
    [ , [ @dynamic_snapshot_jobname = ] N'dynamic_snapshot_jobname' OUTPUT ]
    [ , [ @dynamic_snapshot_jobid = ] 'dynamic_snapshot_jobid' OUTPUT ]
    [ , [ @frequency_type = ] frequency_type ]
    [ , [ @frequency_interval = ] frequency_interval ]
    [ , [ @frequency_subday = ] frequency_subday ]
    [ , [ @frequency_subday_interval = ] frequency_subday_interval ]
    [ , [ @frequency_relative_interval = ] frequency_relative_interval ]
    [ , [ @frequency_recurrence_factor = ] frequency_recurrence_factor ]
    [ , [ @active_start_date = ] active_start_date ]
    [ , [ @active_end_date = ] active_end_date ]
    [ , [ @active_start_time_of_day = ] active_start_time_of_day ]
    [ , [ @active_end_time_of_day = ] active_end_time_of_day ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication to which the filtered data snapshot job is being added. @publication is sysname, with no default.

[ @suser_sname = ] N'suser_sname'

The value used when creating a filtered data snapshot for a subscription that is filtered by the value of the SUSER_SNAME function at the Subscriber. @suser_sname is sysname, with a default of NULL. @suser_sname should be NULL if this function isn't used to dynamically filter the publication.

[ @host_name = ] N'host_name'

The value used when creating a filtered data snapshot for a subscription that is filtered by the value of the HOST_NAME function at the Subscriber. @host_name is sysname, with a default of NULL. host_name should be NULL if this function isn't used to dynamically filter the publication.

[ @dynamic_snapshot_jobname = ] N'dynamic_snapshot_jobname' OUTPUT

The name of the filtered data snapshot job created. @dynamic_snapshot_jobname is an OUTPUT parameter of type sysname. If specified, @dynamic_snapshot_jobname must resolve to a unique job at the Distributor. If unspecified, a job name is automatically generated in the result set, where the name is created as follows:

'dyn_' + <name of the standard snapshot job> + <GUID>

Note

When generating the name of the dynamic snapshot job, you might truncate the name of the standard snapshot job.

[ @dynamic_snapshot_jobid = ] 'dynamic_snapshot_jobid' OUTPUT

An identifier for the filtered data snapshot job created. @dynamic_snapshot_jobid is an OUTPUT parameter of type uniqueidentifier, with a default of NULL.

[ @frequency_type = ] frequency_type

Specifies the frequency by which the filtered data snapshot job is scheduled. @frequency_type is int, and can be one of these values.

Value	Description
1	One time
2 (default)	On demand
4	Daily
8	Weekly
16	Monthly
32	Monthly relative
64	Autostart
128	Recurring

[ @frequency_interval = ] frequency_interval

The period, measured in days, when the filtered data snapshot job is executed. @frequency_interval is int, and depends on the value of @frequency_type.

Value of @frequency_type	Effect on @frequency_interval
1 (default)	@frequency_interval is unused.
4	Every @frequency_interval days.
8	@frequency_interval is one or more of the following (combined with a | (Bitwise OR) logical operator): 1 = Sunday 2 = Monday 4 = Tuesday 8 = Wednesday 16 = Thursday 32 = Friday 64 = Saturday
16	On the @frequency_interval day of the month.
32	@frequency_interval is one of the following options: 1 = Sunday 2 = Monday 3 = Tuesday 4 = Wednesday 5 = Thursday 6 = Friday 7 = Saturday 8 = Day 9 = Weekday 10 = Weekend day
64	@frequency_interval is unused.
128	@frequency_interval is unused.

[ @frequency_subday = ] frequency_subday

Specifies the units for @frequency_subday_interval. @frequency_subday is int, and can be one of these values.

Value	Description
1 (default)	Once
2	Second
4	Minute
8	Hour

[ @frequency_subday_interval = ] frequency_subday_interval

The number of frequency_subday periods that occur between each execution of the job. @frequency_subday_interval is int, with a default of 1.

[ @frequency_relative_interval = ] frequency_relative_interval

The occurrence of the filtered data snapshot job in each month. This parameter is used when @frequency_type is set to 32 (monthly relative). @frequency_relative_interval is int, and can be one of these values.

Value	Description
1 (default)	First
2	Second
4	Third
8	Fourth
16	Last

[ @frequency_recurrence_factor = ] frequency_recurrence_factor

The recurrence factor used by frequency_type. @frequency_recurrence_factor is int, with a default of 1.

[ @active_start_date = ] active_start_date

The date when the filtered data snapshot job is first scheduled, formatted as yyyyMMdd. @active_start_date is int, with a default of 0.

[ @active_end_date = ] active_end_date

The date when the filtered data snapshot job stops being scheduled, formatted as yyyyMMdd. @active_end_date is int, with a default of 0.

[ @active_start_time_of_day = ] active_start_time_of_day

The time of day when the filtered data snapshot job is first scheduled, formatted as HHmmss. @active_start_time_of_day is int, with a default of 0.

[ @active_end_time_of_day = ] active_end_time_of_day

The time of day when the filtered data snapshot job stops being scheduled, formatted as HHmmss. @active_end_time_of_day is int, with a default of 0.

Result set

Column name	Data type	Description
id	int	Identifies the filtered data snapshot job in the MSdynamicsnapshotjobs system table.
dynamic_snapshot_jobname	sysname	Name of the filtered data snapshot job.
dynamic_snapshot_jobid	uniqueidentifier	Uniquely identifies the SQL Server Agent job at the Distributor.

Return code values

0 (success) or 1 (failure).

Remarks

sp_adddynamicsnapshot_job is used in merge replication for publications that use a parameterized filter.

Examples

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks2022';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks2022];

-- Enable AdventureWorks2022 for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication.  
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks2022.', 
  @allow_subscriber_initiated_snapshot = N'false';

-- Create a new snapshot job for the publication, using the 
-- default schedule. Pass credentials at runtime using 
-- sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(Login), 
  @job_password = $(password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains customer information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

-- Start the snapshot agent job.
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';

EXEC sp_startpublication_snapshot 
  @publication = @publication;
GO

PRINT '*** Waiting for the initial snapshot.';
GO

-- Create a temporary table to store the filtered data snapshot 
-- job information.
CREATE TABLE #temp (id int,
    job_name sysname,
    job_id uniqueidentifier,
    dynamic_filter_login sysname NULL,
    dynamic_filter_hostname sysname NULL,
    dynamic_snapshot_location nvarchar(255),
    frequency_type int, 
    frequency_interval int, 
    frequency_subday_type int,
    frequency_subday_interval int, 
    frequency_relative_interval int, 
    frequency_recurrence_factor int, 
    active_start_date int, 
    active_end_date int, 
    active_start_time int, 
    active_end_time int
)

-- Create each snapshot for a partition 
-- The initial snapshot must already be generated.
DECLARE @publication AS sysname;
DECLARE @jobname AS sysname
DECLARE @hostname AS sysname
SET @publication = N'AdvWorksSalesPersonMerge';
SET @hostname = N'adventure-works\Fernando';

WHILE NOT EXISTS(SELECT * FROM sysmergepublications 
    WHERE [name] = @publication 
    AND snapshot_ready = 1)
BEGIN
    WAITFOR DELAY '00:00:05'
END

-- Create a data partition by overriding HOST_NAME().
EXEC sp_addmergepartition 
  @publication = @publication,
  @host_name = @hostname;

-- Create the filtered data snapshot job, and use the returned 
-- information to start the job.
EXEC sp_adddynamicsnapshot_job 
  @publication = @publication,
  @host_name = @hostname;

INSERT INTO #temp (id, job_name, job_id, dynamic_filter_login,
    dynamic_filter_hostname, dynamic_snapshot_location,
    frequency_type,	frequency_interval, frequency_subday_type,
    frequency_subday_interval, frequency_relative_interval, 
    frequency_recurrence_factor, active_start_date,	active_end_date, 
    active_start_time,active_end_time)
EXEC sp_helpdynamicsnapshot_job;

SELECT @jobname = (SELECT DISTINCT job_name FROM #temp WHERE dynamic_filter_hostname = @hostname);

EXEC msdb..sp_start_job @job_name = @jobname;
DROP TABLE #temp;
GO

Permissions

Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_adddynamicsnapshot_job.

Related content

• Create a Snapshot for a Merge Publication with Parameterized Filters
• Parameterized Filters - Parameterized Row Filters
• sp_dropdynamicsnapshot_job (Transact-SQL)
• sp_helpdynamicsnapshot_job (Transact-SQL)