none
How can i truncate a published table?

    Question

  • I need to replicate our production tables to another server. Unfortunately there are 2 refresh jobs that runs at night that truncates the tables completely and refreshes it with fresh data. How can i setup transactional replication or any type of replication? Is there a way around this?

    thanks in Advance!

    Friday, August 20, 2010 2:33 PM

Answers

  • You can't. The only way is to drop the article from the subscription and publication and then do your truncate. Then add it back in.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by JNY Friday, August 20, 2010 5:19 PM
    Friday, August 20, 2010 3:39 PM
    Moderator

All replies

  • You can't. The only way is to drop the article from the subscription and publication and then do your truncate. Then add it back in.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    • Marked as answer by JNY Friday, August 20, 2010 5:19 PM
    Friday, August 20, 2010 3:39 PM
    Moderator
  • i also tried that. thats not possible. By the way: Forex Expert Advisor is a good idea.
    Monday, August 23, 2010 9:20 AM
  • Do you have access to the refresh jobs? If so you can replace the TRUNCATE TABLE with a DELETE statement for this to be supported in transactional replication.
    Cheers,
    Paul
     
    Thursday, September 2, 2010 4:44 PM
  • Do you have an FK relationships on this table? This will prevent a truncate of a table.
    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, September 2, 2010 4:59 PM
    Moderator