Dela via

  • Artikel

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.

Topic link iconTransact-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.

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.

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.