sp_helpdynamicsnapshot_job (Transact-SQL)

 

Applies To: SQL Server

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

Topic link icon Transact-SQL Syntax Conventions

  
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 nameData typeDescription
idintIdentifies the filtered data snapshot job.
job_namesysnameName of the filtered data snapshot job.
job_iduniqueidentifierIdentifies the Microsoft SQL Server Agent job at the Distributor.
dynamic_filter_loginsysnameValue used for evaluating the SUSER_SNAME function in a parameterized row filter defined for the publication.
dynamic_filter_hostnamesysnameValue used for evaluating the HOST_NAME function in a parameterized row filter defined for the publication.
dynamic_snapshot_locationnvarchar(255)Path to the folder where the snapshot files are read from if a parameterized row filter is used.
frequency_typeintIs 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_intervalintThe 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_typeintIs 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_intervalintNumber of intervals of frequency_subday_type that occur between scheduled execution of the agent.
frequency_relative_intervalintIs 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_factorintNumber of weeks or months between the scheduled execution of the agent.
active_start_dateintDate when the agent is first scheduled to run, formatted as YYYYMMDD.
active_end_dateintDate when the agent is last scheduled to run, formatted as YYYYMMDD.
active_start_timeintTime when the agent is first scheduled to run, formatted as HHMMSS.
active_end_timeintTime when the agent is last scheduled to run, formatted as HHMMSS.

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.

System Stored Procedures (Transact-SQL)

Community Additions

ADD
Show: