SQL Server Performance

transactional replication + sql2005 + replicating schema changes with stored procedures

Discussion in 'SQL Server 2005 Replication' started by mandyt, Jul 10, 2009.

  1. mandyt New Member

    Can you replicate different schemas between databases i.e.
    Table1
    Gendev.dbo.Test
    Id First Last
    1 Mand Toms
    2 Nick Barfoot
    Table2
    Docraftsv2.dbo.Testname
    Id First2 Last2
    We would want to populate testing.table2 with the data from Test.table1 keeping the column names the same. I have tried this using Stored Procedures [testrep] --SET IDENTITY_INSERT docraftsV2.dbo.test ONinsert into docraftsV2.dbo.test(firstname2,lastname2)select firstname,lastnamefrom [Animal].[Gendev].[dbo].[testname] Replication Settings:- Subscription Options:- Whilst this works for the initial snapshot, data is populated and column names remain the same, any further updates etc causes this to fall over, have tried to use the same SP in the Update and Delete delivery formats but no joy.
  2. ndinakar Member

    The schema has to match.. however after setting up replication, you can go in and hack those sp_*** procs..
    if you need your table2 to be in sync with table1, modify the sp_insert, update and delete procs to also modify the table2. alternatively, you can use triggers... but lets not go there...
  3. mandyt New Member

    Thanks, have altered the article with custom stored procedures and updated the snapshot and works a treat. Thanks for your assistance

Share This Page