sp_helppublication_snapshot (Transact-SQL)

Returns information on the Snapshot agent for a given publication. This stored procedure is executed at the Publisher on the publication database.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_helppublication_snapshot [ @publication= ] 'publication'
    [ , [ @publisher= ] 'publisher' ]

Arguments

  • [ @publication = ] 'publication'
    Is the name of the publication. publication is sysname, with no default.

  • [ @publisher = ] 'publisher'
    Specifies a non-SQL Server publisher. publisher is sysname, with a default of NULL.

    Note

    publisher should not be used when adding an article to a SQL Server Publisher.

Result Sets

Column name

Data type

Description

id

int

ID of the Snapshot Agent.

name

nvarchar(100)

Name of the Snapshot Agent.

publisher_security_mode

smallint

Security mode used by the agent when connecting to the Publisher, which can be one of the following:

0 = SQL Server Authentication

1 = Windows Authentication.

publisher_login

sysname

Login used when connecting to the Publisher.

publisher_password

nvarchar(524)

For security reasons, a value of ********** is always returned.

job_id

uniqueidentifier

Unique ID of the agent job.

job_login

nvarchar(512)

Is the Windows account under which the Snapshot agent runs, which is returned in the format DOMAIN\username.

job_password

sysname

For security reasons, a value of ********** is always returned.

schedule_name

sysname

Name of the schedule used for this agent job.

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_help_publication_snapshot is used in all types of replication.

Permissions

Only members of the sysadmin fixed server role at the Publisher or members of the db_owner fixed database role on the publication database can execute sp_help_publication_snapshot.