syspublications (System View) (Transact-SQL)
The syspublications view exposes publication information. This view is stored in the distribution database.
Column name
Data type
Description
description
nvarchar(255)
The descriptive entry for the publication.
name
sysname
The unique name associated with the publication.
pubid
int
The identity column providing a unique ID for the publication.
repl_freq
tinyint
The replication frequency:
0 = Transaction based (transactional).
1 = Scheduled table refresh (snapshot).
status
tinyint
The publication status:
0 = Inactive.
1 = Active.
sync_method
tinyint
The synchronization method:
0 = Native bulk copy program utility (BCP).
1 = Character BCP.
3 = Concurrent, which means that native BCP is used but tables are not locked during the snapshot.
4 = Concurrent_c, which means that character BCP is used but tables are not locked during the snapshot.
5 = Database Snapshot, which means that native-mode BCP is used tp copy data from a database snapshot.
6 = Database Snapshot Character, which means that character-mode BCP is used tp copy data from a database snapshot.
snapshot_jobid
binary(16)
Identifies the agent job scheduled to generate the initial snapshot.
independent_agent
bit
Specifies whether there is a stand-alone Distribution Agent for this publication.
0 = The publication uses a shared Distribution Agent, and each Publisher database/Subscriber database pair has a single, shared Agent.
1 = There is a stand-alone Distribution Agent for this publication.
immediate_sync
bit
Indicates whether the synchronization files are created or recreated each time the Snapshot Agent runs, where 1 means that they are created every time the agent runs.
enabled_for_internet
bit
Indicates whether the synchronization files for the publication are exposed to the Internet through file transfer protocol (FTP) and other services, where 1 means that they can be accessed from the Internet.
allow_push
bit
Indicates whether push subscriptions are allowed on the publication, where 1 means that they are allowed.
allow_pull
bit
Indicates whether pull subscriptions are allowed on the publication, where 1 means that they are allowed.
allow_anonymous
bit
Indicates whether anonymous subscriptions are allowed on the publication, where 1 means that they are allowed.
immediate_sync_ready
bit
Indicates whether the snapshot has been generated by the Snapshot Agent and is ready for use by new subscriptions. It is only meaningful for immediate updating publications. 1 indicates that the snapshot is ready.
allow_sync_tran
bit
Specifies whether immediate-updating subscriptions are allowed on the publication. 1 means that immediate-updating subscriptions are allowed.
autogen_sync_procs
bit
Specifies whether the synchronizing stored procedure for immediate-updating subscriptions is generated at the Publisher. 1 means that it is generated at the Publisher.
retention
int
The amount of time, in hours, that changes to the publication are maintained in the distribution database.
allow_queued_tran
bit
Specifies whether queuing of changes at the Subscriber until they can be applied at the Publisher has been enabled. If 1, changes at the Subscriber are queued.
snapshot_in_defaultfolder
bit
Specifies whether snapshot files are stored in the default folder. If 0, snapshot files have been stored in the alternate location specified by alternate_snapshot_folder. If 1, snapshot files can be found in the default folder.
alt_snapshot_folder
nvarchar(510)
Specifies the location of the alternate folder for the snapshot.
pre_snapshot_script
nvarchar(510)
Specifies a pointer to a .sql file location. The Distribution Agent runs the pre-snapshot script before running any of the replicated object scripts when applying a snapshot at a Subscriber.
post_snapshot_script
nvarchar(510)
Specifies a pointer to a .sql file location. The Distribution Agent runs the post-snapshot script after all the other replicated object scripts and data have been applied during an initial synchronization.
compress_snapshot
bit
Specifies that the snapshot that is written to the alt_snapshot_folder location is to be compressed into the Microsoft CAB format. 1 means that the snapshot will be compressed.
ftp_address
sysname
The network address of the FTP service for the Distributor. Specifies where publication snapshot files are located for the Distribution Agent to pick up.
ftp_port
int
The port number of the FTP service for the Distributor. Specifies where the publication snapshot files are located for the Distribution Agent to pick up.
ftp_subdirectory
nvarchar(510)
Specifies where the snapshot files are available for the Distribution Agent to pick up if the publication supports propagating snapshots using FTP.
ftp_login
nvarchar(256)
The username used to connect to the FTP service.
ftp_password
nvarchar(1048)
The user password used to connect to the FTP service.
allow_dts
bit
Specifies whether the publication allows SQL Server 2000 Data Transformation Services (DTS) transformations. 1 specifies that DTS transformations are allowed.
allow_subscription_copy
bit
Specifies whether the ability to copy the subscription databases that subscribe to this publication has been enabled. 1 means that copying is allowed.
centralized_conflicts
bit
Specifies whether conflict records are stored on the Publisher:
0 = Conflict records are stored at both the publisher and at the subscriber that caused the conflict.
1 = Conflict records are stored at the Publisher.
conflict_retention
int
Specifies the retention period for conflict records, in days.
conflict_policy
int
Specifies the conflict resolution policy followed when the queued updating subscriber option is used. Can be one of these values:
1 = Publisher wins the conflict.
2 = Subscriber wins the conflict.
3 = Subscription is reinitialized.
queue_type
int
Specifies which type of queue is used. Can be one of these values:
1 = .msmq, which uses Microsoft Message Queuing to store transactions.
2 = .sql, which uses SQL Server to store transactions.
.gif "ms189805.note(en-US,SQL.90).gif")
Note:
Using Microsoft Message Queuing has been deprecated and is no longer supported.
ad_guidname
sysname
Specifies whether the publication is published in the Microsoft Active Directory. A valid globally-unique identifier (GUID) specifies that the publication is published in the Active Directory, and the GUID is the corresponding Active Directory publication object objectGUID. If NULL, the publication is not published in Active Directory.
Note:
Publishing to Active Directory is no longer supported.
backward_comp_level
int
Database compatibility level, which can be one of the following values:
10 = SQL Server 7.0.
20 = SQL Server 7.0 Service Pack (SP) 1.
30 = SQL Server 7.0 SP 2.
35 = SQL Server 7.0 SP 3.
40 = SQL Server 2000.
50 = SQL Server 2000 SP 1.
60 = SQL Server 2000 SP 3.
90 = SQL Server 2005.
allow_initialize_from_backup
bit
Indicates whether Subscribers can initialize a subscription to this publication from a backup rather than an initial snapshot. 1 means that subscriptions can be initialized from a backup, and 0 means that they cannot. For more information, see Initializing a Transactional Subscription Without a Snapshot.
min_autonosync_lsn
binary(1)
For nternal use only.
replicate_ddl
int
Indicates whether schema replication is supported for the publication.
1 = DDL statements executed at the publisher are replicated.
0 = Indicates that DDL statements are not replicated. For more information, see Making Schema Changes on Publication Databases.
options
int
The bitmap that specifies additional publishing options, where the bitwise option values are:
0x1 - Enabled for peer-to-peer replication.
0x2 - Publish only local changes.
0x4 - Enabled for non-SQL Server subscribers.
See Also
Reference
Replication System Stored Procedures (Transact-SQL)
sp_addpublication (Transact-SQL)
sp_changepublication (Transact-SQL)
sp_helppublication (Transact-SQL)
Concepts
Replication Tables (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|