Skip to content

Latest commit

 

History

History
94 lines (71 loc) · 5.54 KB

sp-helpdynamicsnapshot-job-transact-sql.md

File metadata and controls

94 lines (71 loc) · 5.54 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_helpdynamicsnapshot_job (Transact-SQL)
sp_helpdynamicsnapshot_job returns information on agent jobs that generate filtered data snapshots.
markingmyname
maghan
randolphwest
05/15/2024
sql
replication
reference
sp_helpdynamicsnapshot_TSQL
sp_helpdynamicsnapshot_job_TSQL
job_TSQL
helpdynamicsnapshot
job
sp_helpdynamicsnapshot
sp_helpdynamicsnapshot_job
helpdynamicsnapshot_TSQL
sp_helpdynamicsnapshot_job
TSQL

sp_helpdynamicsnapshot_job (Transact-SQL)

[!INCLUDE SQL Server SQL MI]

Returns information on agent jobs that generate filtered data snapshots. This stored procedure is executed at the Publisher on the publication database.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_helpdynamicsnapshot_job
    [ [ @publication = ] N'publication' ]
    [ , [ @dynamic_snapshot_jobname = ] N'dynamic_snapshot_jobname' ]
    [ , [ @dynamic_snapshot_jobid = ] 'dynamic_snapshot_jobid' ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication. @publication is sysname, with a default of %, which returns information on all filtered data snapshot jobs that match the specified @dynamic_snapshot_jobid and @dynamic_snapshot_jobname for all publications.

[ @dynamic_snapshot_jobname = ] N'dynamic_snapshot_jobname'

The name of a filtered data snapshot job. @dynamic_snapshot_jobname is sysname, with a default of %, which returns all dynamic jobs for a publication with the specified @dynamic_snapshot_jobname. If a job name wasn't explicitly specified when the job was created, the job name is in the format 'dyn_' + <name of the standard snapshot job> + <GUID>.

[ @dynamic_snapshot_jobid = ] 'dynamic_snapshot_jobid'

An identifier for a filtered data snapshot job. @dynamic_snapshot_jobid is uniqueidentifier, with a default of NULL, which returns all snapshot jobs that match the specified @dynamic_snapshot_jobname.

Result set

Column name Data type Description
id int Identifies the filtered data snapshot job.
job_name sysname Name of the filtered data snapshot job.
job_id uniqueidentifier Identifies the [!INCLUDE ssNoVersion] Agent job at the Distributor.
dynamic_filter_login sysname Value used for evaluating the SUSER_SNAME function in a parameterized row filter defined for the publication.
dynamic_filter_hostname sysname Value used for evaluating the HOST_NAME function in a parameterized row filter defined for the publication.
dynamic_snapshot_location nvarchar(255) Path to the folder where the snapshot files are read from if a parameterized row filter is used.
frequency_type int Is the frequency with which the agent is scheduled to run, which can be one of these values.

1 = One time
2 = On demand
4 = Daily
8 = Weekly
16 = Monthly
32 = Monthly relative
64 = Autostart
128 = Recurring
frequency_interval int The days that the agent runs, which can be one of these values.

1 = Sunday
2 = Monday
3 = Tuesday
4 = Wednesday
5 = Thursday
6 = Friday
7 = Saturday
8 = Day
9 = Weekdays
10 = Weekend days
frequency_subday_type int Is the type that defines how often the agent runs when frequency_type is 4 (daily), and can be one of these values.

1 = At the specified time
2 = Seconds
4 = Minutes
8 = Hours
frequency_subday_interval int Number of intervals of frequency_subday_type that occur between scheduled execution of the agent.
frequency_relative_interval int Is the week that the agent runs in a given month when frequency_type is 32 (monthly relative), and can be one of these values.

1 = First
2 = Second
4 = Third
8 = Fourth
16 = Last
frequency_recurrence_factor int Number of weeks or months between the scheduled execution of the agent.
active_start_date int Date when the agent is first scheduled to run, formatted as yyyyMMdd.
active_end_date int Date when the agent is last scheduled to run, formatted as yyyyMMdd.
active_start_time int Time when the agent is first scheduled to run, formatted as HHmmss.
active_end_time int Time when the agent is last scheduled to run, formatted as HHmmss.

Return code values

0 (success) or 1 (failure).

Remarks

sp_helpdynamicsnapshot_job is used in merge replication.

If all of the default parameter values are used, information on all partitioned data snapshot jobs for the entire publication database is returned.

Permissions

Only members of the sysadmin fixed server role, the db_owner fixed database role, and the publication access list for the publication can execute sp_helpdynamicsnapshot_job.

Related content