sp_helppullsubscription

Transact-SQL Reference

Transact-SQL Reference

sp_helppullsubscription

Displays information about one or more subscriptions at the Subscriber. This stored procedure is executed at the Subscriber on the subscription database.

Syntax

sp_helppullsubscription [ [ @publisher = ] 'publisher' ]
    [ , [ @publisher_db = ] 'publisher_db' ]
    [ , [ @publication = ] 'publication' ]
    [ , [ @show_push = ] 'show_push' ]

Arguments

[@publisher =] 'publisher'

Is the name of the remote server. publisher is sysname, with a default of %, which returns all the Publishers.

[@publisher_db =] 'publisher_db'

Is the name of the Publisher database. publisher_db is sysname, with a default of %, which returns all the Publisher databases.

[@publication =] 'publication'

Is the name of the publication. publication is sysname, with a default of %, which returns all the publications.

[@show_push =] 'show_push'

Is whether all push subscriptions are to be returned. show_push is nvarchar(5), with a default of FALSE, which does not return all push subscriptions.

Result Sets
Column name Data type Description
publisher sysname Name of the Publisher.
publisher database sysname Name of the Publisher database.
publication sysname Name of the publication.
independent_agent bit Indicates whether there is a stand-alone Distribution Agent for this publication.
subscription type int Subscription type to the publication.
distribution agent nvarchar(100) Distribution Agent handling the subscription.
publication description nvarchar(255) Description of the publication.
last updating time date Time the subscription information was updated. This is a UNICODE string of ISO date (114) + ODBC time (121). The format is yyyymmdd hh:mi:sss.mmm where 'yyyy' is year, 'mm' is month, 'dd' is day, 'hh' is hour, 'mi' is minute, 'sss' is seconds, 'mmm' is milliseconds.
subscription name varchar(386) Name of the subscription.
last transaction timestamp varbinary(16) Timestamp of the last replicated transaction.
update mode tinyint Type of updates allowed.
distribution agent job_id int Job ID of the Distribution Agent.
enabled_for_synmgr int Whether the subscription can be synchronized through the Microsoft® Synchronization Manager.
subscription guid binary(16) Global identifier for the version of the subscription on the publication.
subid binary(16) Global identifier for an anonymous subscription.
immediate_sync bit Whether the synchronization files are created or re-created each time the Snapshot Agent runs.
publisher login sysname Login ID used at the Publisher for SQL Server Authentication.
publisher password nvarchar(524) Password (encrypted) used at the Publisher for SQL Server Authentication.
publisher security_mode int Security mode implemented at the Publisher:

0 = SQL Server Authentication
1 = Windows Authentication
2 = The synchronization triggers use a static sysservers entry to do RPC, and publisher must be defined in the sysservers table as a remote server or linked server.

distributor sysname Name of the Distributor.
distributor_login sysname Login ID used at the Distributor for SQL Server Authentication.
distributor_password nvarchar(524) Password (encrypted) used at the Distributor for SQL Server Authentication.
distributor_security_mode int Security mode implemented at the Distributor:

0 = SQL Server Authentication
1 = Windows Authentication

ftp_address sysname For backward compatibility only.
ftp_port int For backward compatibility only.
ftp_login sysname For backward compatibility only.
ftp_password nvarchar(524) For backward compatibility only.
alt_snapshot_folder nvarchar(255) Location where snapshot folder is stored if the location is other than or in addition to the default location.
working_directory nvarchar(255) Fully qualified path to the directory where snapshot files are transferred using FTP when that option is specified.
use_ftp bit Subscription is subscribing to Publication over the Internet and FTP addressing properties are configured. If 0, Subscription is not using FTP. If 1, subscription is using FTP.
publication_type int Specifies the replication type of the publication:

0 = Transactional replication
1 = Snapshot replication
2 = Merge replication

dts_package_name sysname Specifies the name of the DTS package.
dts_package_location int Location where the DTS package is stored:

0 = Distributor
1 = Subscriber

offload_agent bit Specifies if the agent can be activated remotely. If 0, the agent cannot be activated remotely.
offload_server sysname Specifies the network name of the server used for remote activation.
last_sync_status int Subscription status:

0 = All jobs are waiting to start
1 = One or more jobs are starting
2 = All jobs have executed successfully
3 = At least one job is executing
4 = All jobs are scheduled and idle
5 = At least one job is attempting to execute after a previous failure
6 = At least one job has failed to execute successfully

last_sync_summary sysname Description of last synchronization results.
last_sync_time datetime Time the subscription information was updated. This is a UNICODE string of ISO date (114) + ODBC time (121). The format is yyyymmdd hh:mi:sss.mmm where 'yyyy' is year, 'mm' is month, 'dd' is day, 'hh' is hour, 'mi' is minute, 'sss' is seconds, 'mmm' is milliseconds.

Remarks

sp_helppullsubscription is used in snapshot and transactional replication.

Permissions

Execute permissions default to the public role.

See Also

sp_addpullsubscription

sp_droppullsubscription

System Stored Procedures