SQL Server Performance

Replicate those table witout Primary Key

Discussion in 'Performance Tuning for SQL Server Replication' started by ajitgadge, Aug 7, 2007.

  1. ajitgadge New Member

    Hello Friends,
    I have setup Transactional replication in our Env. to replicate live server to report server. We have total 404 tables (Articales) which need to replicate on report server. But only 303 tables have PK and remain not.I have succssfully added and configure those 303 tables and now replication in Sync wih min latency. But now i want to Add/Replicate those tables also which don't have PK without change my origional Schema (Live Server). Is there any way to add those articales on Transactional Replication ?.
    Currently, Database don't have much load and it is in deployment stage so i have prvilage to make a changes.TFYI.
  2. ndinakar Member

    Nope. not possible. You can look into alternate ways. IF the tables are small, perhaps you can write a DTS Package to insert/update tables at the subscriber.
  3. ajitgadge New Member

    Thanks, I have write a query which delete data from subscriber table which don't have PK first and then insert data from publisher.Ex:.
    Delete
    FROM OPENROWSET('MSDASQL',
    'DRIVER={SQL Server};SERVER=Pund560;UID=sa;PWD=sa',
    northwind1.dbo.test1)
    Go

    insert into northwind1.dbo.test1
    select * from OPENROWSET('SQLoledb','Server=pund257;uid=sa;pwd=sa',
    'select * from northwind.dbo.test1')
    Go
    I have attahced this script in Publisher properties (Properties -->SnapShot--> Excute Script After Snapshot). When i start distributor agent for synchronization it gives me an error.
    Failed to apply a script using 'OSQL' Utility.
    However when i checked on target table, Data is deleted but not inserted that mean Delete part of script is working but not Insert.
    Anyone know why it is happening?
    Please let me know other way to insert data using Transactional replication only so that i can manage the thing simply.

Share This Page