sp_helpdynamicsnapshot_job (Transact-SQL)

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 = ] 'publication' ] 
    [ , [ @dynamic_snapshot_jobname = ] 'dynamic_snapshot_jobname' ]
    [ , [ @dynamic_snapshot_jobid = ] 'dynamic_snapshot_jobid' ]

Arguments

• [ @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.

Return Code Values

0 (success) or 1 (failure)

Result Sets

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.

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.

See Also

Reference

System Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance