Create Views and Establish Replication Programmatically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Create Views and Establish Replication Programmatically

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-02 : 08:27:12

Eric writes "I want to programmatically establish merge replication for a database with 250+ tables. However, merge replication adds a uniqueidentifier column to every table. This breaks our legacy application, because it performs INSERTS without column lists.

To get around this, we can rename the tables and create views with the original names. Then we can establish replication without breaking the application.

This is a very time-consuming manual process. Is there some way to automate it? I would need the script to:

1. Rename all user-defined tables in the database by prepending them with an underscore.

2. Create a view for each table with its original name.

3. Publish the database for merge replication."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 12:34:18

Create replication manually for a couple of tables/views as you have been. Then script it out by going to the publication and generating the script. Then you'll see the code necessary to create replication.

Here is an example:

-- Enabling the replication database
use master
GO

exec sp_replicationdboption @dbname = N'GT_QTRACS', @optname = N'publish', @value = N'true'
GO

use [GT_QTRACS]
GO

-- Adding the transactional publication
exec sp_addpublication @publication = N'GT_QTRACS_compopt', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of GT_QTRACS.dbo.compopt table.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @logreader_job_name = N'SDDEVSQL3\GTW-GT_QTRACS-16'
exec sp_addpublication_snapshot @publication = N'GT_QTRACS_compopt',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @snapshot_job_name = N'SDDEVSQL3\GTW-GT_QTRACS-GT_QTRACS_compopt-32'
GO

exec sp_grant_publication_access @publication = N'GT_QTRACS_compopt', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'GT_QTRACS_compopt', @login = N'NA\qwbssqla'
GO
exec sp_grant_publication_access @publication = N'GT_QTRACS_compopt', @login = N'sa'
GO

-- Adding the transactional articles
exec sp_addarticle @publication = N'GT_QTRACS_compopt', @article = N'compopt', @source_owner = N'dbo', @source_object = N'compopt', @destination_table = N'compopt', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false', @ins_cmd = N'SQL', @del_cmd = N'SQL', @upd_cmd = N'SQL', @filter = null, @sync_object = null, @auto_identity_range = N'false'
GO

-- Adding the transactional subscription
exec sp_addsubscription @publication = N'GT_QTRACS_compopt', @article = N'all', @subscriber = N'SDDEVSQL3\GTW', @destination_db = N'GT', @sync_type = N'automatic', @update_mode = N'read only', @offloadagent = 0, @dts_package_location = N'distributor'
GO

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources