Post reply

Replication

  • October 28, 2008 at 4:15 am

    #214629

    Is this possible , to replicate a table with in the same database?

  • October 28, 2008 at 4:47 am

    #890490

    Not that I am aware of. There maybe a trick out there to do this. Anyways, you get an error message indicating the that subscription database is the same as the publisher database.

    If you need to keep 2 tables in sync in the same database you could use a trigger.

  • October 28, 2008 at 6:56 am

    #890581

    of course you can't repl from svr1.db1.dbo.tbl1 -> svr1.db1.dbo.tbl1

    - i.e. pub source to same sub target

    but you can do to

    -> svr1.db1.dbo.tbl2

    -> svr1.db1.xyz.tbl1

    -> svr1.db2.dbo.tbl1

    -> svr2.db1.dbo.tbl1

    where svr2 could be another instance on the same box

    HTH

    Dick

  • October 28, 2008 at 7:05 am

    #890590

    Dear Dick,

    i tried to vreate replication between svr1.db1.dbo.tbl1 -> svr1.db1.dbo.tbl2

    while creating the new subscription i got the error

    "subscription database is the same as the publisher database"

    Pls advise me

    Regards,

    N.Prabhakaran

  • October 28, 2008 at 8:26 am

    #890662

    the SSMS (GUI) wizard is over-protective in this case ...

    you can use SSMS to replicate

    svr1.db1.dbo.tbl1 -> svr1.db2.dbo.tbl1

    and produce scripts. then edit the subscription script to change all "db2.dbo.tbl1" to "db1.dbo.tbl2" (and don't forget to put in the password)

    you can successfully run that edited script in SSMS and re-run Snapshot job

    means you'd get 2 subs and you can delete the older to leave just

    svr1.db1.dbo.tbl1 -> svr1.db1.dbo.tbl2

    and you can rescript if you'd ever need this again

    Dick

    PS I've just done it, so here's final script

    -- Dropping the transactional subscriptions

    use [DickPub]

    go

    exec sp_dropsubscription @publication = N'Prabhakaran', @article = N'all', @subscriber = N'all', @destination_db = N'all'

    exec sp_droparticle @publication = N'Prabhakaran', @article = N'all', @force_invalidate_snapshot = 1

    exec sp_droppublication @publication = N'Prabhakaran'

    GO

    -- Enabling the replication database

    use master

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

    GO

    exec [DickPub].sys.sp_addlogreader_agent @job_login = N'MYDOMAIN\sqlrep', @job_password = 'MYPWD', @publisher_security_mode = 1

    GO

    -- Adding the transactional publication

    exec sp_addpublication @publication = N'Prabhakaran', @description = N'Transactional publication of database ''DickPub'' from Publisher ''MYBOX''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = 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', @replicate_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'Prabhakaran', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'MYDOMAIN\sqlrep', @job_password = null, @publisher_security_mode = 1

    exec sp_grant_publication_access @publication = N'Prabhakaran', @login = N'sa'

    exec sp_grant_publication_access @publication = N'Prabhakaran', @login = N'distributor_admin'

    GO

    -- Adding the transactional articles

    exec sp_addarticle @publication = N'Prabhakaran', @article = N't_Demo', @source_owner = N'dbo', @source_object = N't_Demo', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N't_Demo2', @destination_owner = N'dbo', @status = 8, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbot_Demo]', @del_cmd = N'CALL [sp_MSdel_dbot_Demo]', @upd_cmd = N'SCALL [sp_MSupd_dbot_Demo]'

    GO

    -- Adding the transactional subscriptions

    exec sp_addsubscription @publication = N'Prabhakaran', @subscriber = N'MYBOX', @destination_db = N'DickPub', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

    exec sp_addpushsubscription_agent @publication = N'Prabhakaran', @subscriber = N'MYBOX', @subscriber_db = N'DickPub', @job_login = N'MYDOMAIN\sqlrep', @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'

    GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply