Edit

Share via

sys.sp_helppublication_snapshot (Transact-SQL)

Applies to: SQL Server Azure SQL Managed Instance

Returns information on the Snapshot agent for a given publication. Execute this stored procedure at the Publisher on the publication database.

Transact-SQL syntax conventions

Syntax

sys.sp_helppublication_snapshot
    [ @publication = ] N'publication'
    [ , [ @publisher = ] N'publisher' ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication. @publication is sysname, with no default.

[ @publisher = ] N'publisher'

Specifies a non-SQL Server publisher. @publisher is sysname, with a default of NULL.

Don't use @publisher when you add an article to a SQL Server Publisher.

Result set

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 the agent uses when connecting to the Publisher. It can be one of the following values:

0 = SQL Server Authentication
1 = Windows Authentication.
publisher_login sysname Login used when connecting to the Publisher.
publisher_password nvarchar(524) For security reasons, this column always returns a value of **********.
job_id uniqueidentifier Unique ID of the agent job.
job_login nvarchar(512) The Windows account under which the Snapshot agent runs. The column returns this value in the format <domain>\<username>.
job_password sysname For security reasons, this column always returns a value of **********.
schedule_name sysname The name of the schedule used for this agent job.
frequency_type int The frequency with which the agent is scheduled to run. It 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 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 The number of intervals of frequency_subday_type that occur between scheduled execution of the agent.
frequency_relative_interval int 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 The number of weeks or months between the scheduled execution of the agent.
active_start_date int The date when the agent is first scheduled to run, formatted as yyyyMMdd.
active_end_date int The date when the agent is last scheduled to run, formatted as yyyyMMdd.
active_start_time int The time when the agent is first scheduled to run, formatted as HHmmss.
active_end_time int The 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 run sp_help_publication_snapshot.

Related content

  • Last updated on