Returns information on agent jobs that generate filtered data snapshots. This stored procedure is executed at the Publisher on the publication database.
sp_helpdynamicsnapshot_job [ [ @publication = ] 'publication' ] [ , [ @dynamic_snapshot_jobname = ] 'dynamic_snapshot_jobname' ] [ , [ @dynamic_snapshot_jobid = ] 'dynamic_snapshot_jobid' ]
[ @publication = ] 'publication'
Is 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_jobidand dynamic_snapshot_jobnamefor all publications.
[ @dynamic_snapshot_jobname = ] 'dynamic_snapshot_jobname'
Is the name of a filtered data snapshot job. dynamic_snapshot_jobnameis sysname, with default of %', which returns all dynamic jobs for a publication with the specified dynamic_snapshot_jobid. If a job name was not explicitly specified when the job was created, the job name is in the following format:
'dyn_' + <name of the standard snapshot job> + <GUID>
[ @dynamic_snapshot_jobid = ] 'dynamic_snapshot_jobid'
Is an identifier for a filtered data snapshot job. dynamic_snapshot_jobidis uniqueidentifier, with default of NULL, which returns all snapshot jobs that match the specified dynamic_snapshot_jobname.
|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 Microsoft 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)
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.
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.