Create transactional publication in sql server 2008

Hi all,
Today we are going to learn how to create a transactional publication in sql server 2008. You may create it by using gui of sql server management studio. But we learn using sql script. Just copy the following code and enjoy…

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

exec sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1

exec sp_addpublication
@publication = N'publication_name',
@description = N'Transactional publication of database ''testdb'' from publisher ''hasib\sql2008''. ',
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true',
@allow_annonymous = N'true', @enabled_for_internet = N'false',
@snapshot_in_defalultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false',
@repl_freq = N'continuous', @status = N'active', @independent_agent = N'true',
@immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false',
@allow_queued_tran = N'false', @allow_dts = N'false', @replicated_ddl = 1,
@allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'

exec sp_addpublication_snapshot
@publication = N'publication_name', @frequency_type = 0, @frequency_interval = 1,
@frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday =0,
@frequency_subday_interval = 1, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0,
@job_login = null, @job_password = null, @publisher_security_mode = 1

go

declare @name nvarchar(50)
declare curname cursor for
select name from sysobjects where type = 'U'
open curname
fetch next from curname into @name
while @@FETCH_STATUS = 0
begin
if exists(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name AND TABLE_SCHEMA = 'dbo')
begin
exec sp_addarticle
@publication = N'publication_name', @article = @name, @source_owner = N'dbo',
@source_object = @name, @type = N'logbased', @description = null, @creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'manual', @destination_table = @name,
@destination_owner = N'dbo', @vertical_partition = N''
end
fetch next from curname into @name
end
close curname
deallocate curname

Thats all.

Bye

About these ads