Snapshot - DROP the Existing table and Recreate it

• weirdscientist78

  SSC Veteran

  Points: 207

  More actions

  April 7, 2010 at 11:04 am

  #231476

  I have two machines running SQL SERVER 2000

  and one other SQL SERVER 2008.

  I want to setup Snapshot Replication of

  6 Databases between the three.

  The issue I am having is more like a pain than an issue.

  I have a little over 100 tables in each of these databases.

  Now, while creating Snapshots for the Articles(or tables),

  there are several ways you can handle name conflicts as in:

  

  (if you cant view image,click here:=http://tinypic.com/r/vzzls6/5])

  By default, it DROPS the existing table and re-creates it.

  I want it to "Delete all data in the existing table".

  I can change this manually for each article (or table), but there are over a 100 tables;

  and besides each time it drops and recreates the table, the permissions get all messed up.

  Is there a way I can change one table and make the change propagate to the rest of

  the tables?

  any help would be appreciated. thanks in advance

• bhadups

  SSC Journeyman

  Points: 77

  More actions

  May 26, 2010 at 4:37 pm

  #1173061

  I was also looking for the same discovered and modified the base table to facilitate it.

  I feel there's some bug , even if you change the option in GUI tool, it may or may not take it.

  sometimes I tried the same thing multiple times to make it work.

  Now this is what i do:

  First create publication - do not push subscription.

  open a query analyzer,

  use publication db

  select dest_table,pre_creation_cmd from sysextendedarticlesview

  Here check - the value of pre_creation_cmd

  The pre-creation command for DROP TABLE, DELETE TABLE, or TRUNCATE:

  0 = None.

  1 = DROP.

  2 = DELETE.

  3 = TRUNCATE.

  In my case I wanted to keep the existing table unchanged,so

  update sysextendedarticlesview set pre_creation_cmd=0;

  Now if I push subscriptions, i get the desired results.

  Hope this helps,

  shakti bhadupotey