Edit

Share via

sp_helpdynamicsnapshot_job (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

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

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 SQL Server 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