Creating a Publication with SQL Statements

The following is an example that shows the creation of the publication shown above using SQL statements, rather than the wizard.

The following stored procedures are used:

  • sp_replicationdboption sets database options for replication.

  • sp_addpublication creates and configures the publication.

  • sp_addpublication_shapshot creates the snapshot agent.

  • sp_addarticle will add the various articles to the publication.

The example is shown here:

use [AdventureWorks] exec sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'publish', @value = N'true' GO use [AdventureWorks] exec sp_addpublication @publication = N'AWPub', @description = N'Transactional publication of database' 'AdventureWorks' 'from Publisher' ...

Get Microsoft® SQL Server™ 2005 Administrator's Companion now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.