RE: Indexes and keys are not synced w/ snapshot publication

Hi Johnny,

The indexes, constraints should be copied to the subscriber even though the
destination object name is different than source object name. I suspect that
you probably still have the "old" table with the source table name and the
same constraints at the subscriber. What happens in this case is that since
constraint names have to be unique across all tables, the distribution agent
simply cannot create a constraint on the "new" table with the same name as
one on the "old" table.

-Raymond

"Johnny" wrote:

> UPDATE: I just did some additional testing and found that if the destination
> table stays the same as the source, the PKs and the indexes are copied to the
> subscriber as they should be. It seems like this problem only occurs when the
> destination table is different.
>
> Does anyone know if and how I can work around this or force it to include
> them even though the destination table is different?
>
> Johnny
>
>
>
> "Johnny" wrote:
>
> > Hello,
> >
> > I am having trouble getting the indexes and primary keys copied to the
> > subscriber in a snapshot publication. I created a snapshot publication and
> > added an article to it using the wizard. Then I used the SQL Management
> > Studio to configure the properties of the article and set the following
> > options to true.
> >
> > Copy primary key
> > Copy clustered index
> > Copy nonclustered indexes
> > Copy check constraints
> > Copy foreign keys
> >
> >
> > I generated the SQL script and got the code listed below to re-create the
> > article. When I use this script to create the article, the settings mentioned
> > above are "true" as expected. However, when the snapshot is applied to the
> > subscriber, none of the keys or indexes are transferred eventough everything
> > suceeds.
> >
> > Does anyone know what else I can do to make this work? Did I not configure
> > it correctly?
> >
> >
> > Here is the code to recreate the article.
> > ---------------------------------------------------
> > exec sp_addarticle
> > @publication = @PUBLICATON_NAME,
> > @article = @TABLE_NAME,
> > @source_owner = N'dbo',
> > @source_object = @TABLE_NAME,
> > @type = N'logbased',
> > @description = null,
> > @creation_script = null,
> > @pre_creation_cmd = N'drop',
> > @schema_option = 0x00000000080350DD,
> > @identityrangemanagementoption = N'none',
> > @destination_table = @DESTINATION_TABLE_NAME,
> > @destination_owner = N'dbo',
> > @vertical_partition = N'false'
> >
> >
> > P.S - FYI, the destination table name is different than the source table
> > name as per our biz requirements. Could this be why?
> >
> > Thanks!
> > Johnny
.

Relevant Pages

  • RE: Indexes and keys are not synced w/ snapshot publication
    ... all indexes, primary keys, etc. were successfully transferred. ... > destination object name is different than source object name. ... > same constraints at the subscriber. ...
    (microsoft.public.sqlserver.replication)
  • Re: isolation level serializable
    ... end loop; ... on to disable all constraints on the destination tables, ... set transaction isolation level serializable; ...
    (comp.databases.oracle.server)
  • Re: Merge replication with foreign key constraints
    ... > constraints is that you might add an row to a child table which you are ... > replicating on the publisher where the row exists on the parent table. ... > on the subscriber and publisher. ... REPLICATION, so if replication job first inserts child table - it will work. ...
    (microsoft.public.sqlserver.replication)
  • On Error Resume Next equllance in VB.NET?
    ... Constraints in the destination table, it Throws an Exception and Skip the ... Here is my Error Trapping procedure ...
    (microsoft.public.dotnet.languages.vb)
  • Mapping 2 nodes into one... how?
    ... I'm trying to map content from 2 different nodes in the source document ... A conceptual example would be as follows, with the "subscriber" nodes ... The destination source would then look like this: ... I've tried skipping looping functoids of all sorts, ...
    (microsoft.public.biztalk.general)
Quantcast