| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | ||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_adddynamicsnapshot_job (Transact-SQL) |
Creates an agent job that generates a filtered data snapshot for a publication with parameterized row filters. |
mashamsft |
mathoma |
randolphwest |
08/22/2024 |
sql |
replication |
reference |
|
|
|
[!INCLUDE SQL Server SQL MI]
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.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
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 ]
[ ; ]
The name of the publication to which the filtered data snapshot job is being added. @publication is sysname, with no default.
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.
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.
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.
An identifier for the filtered data snapshot job created. @dynamic_snapshot_jobid is an OUTPUT parameter of type uniqueidentifier, with a default of NULL.
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 |
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 = Sunday2 = Monday4 = Tuesday8 = Wednesday16 = Thursday32 = Friday64 = Saturday |
16 |
On the @frequency_interval day of the month. |
32 |
@frequency_interval is one of the following options:1 = Sunday2 = Monday3 = Tuesday4 = Wednesday5 = Thursday6 = Friday7 = Saturday8 = Day9 = Weekday10 = Weekend day |
64 |
@frequency_interval is unused. |
128 |
@frequency_interval is unused. |
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 |
The number of frequency_subday periods that occur between each execution of the job. @frequency_subday_interval is int, with a default of 1.
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 |
The recurrence factor used by frequency_type. @frequency_recurrence_factor is int, with a default of 1.
The date when the filtered data snapshot job is first scheduled, formatted as yyyyMMdd. @active_start_date is int, with a default of 0.
The date when the filtered data snapshot job stops being scheduled, formatted as yyyyMMdd. @active_end_date is int, with a default of 0.
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.
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.
| 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 [!INCLUDE ssNoVersion] Agent job at the Distributor. |
0 (success) or 1 (failure).
sp_adddynamicsnapshot_job is used in merge replication for publications that use a parameterized filter.
:::code language="sql" source="../replication/codesnippet/tsql/sp-adddynamicsnapshot-jo_1.sql":::
Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_adddynamicsnapshot_job.