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

Wow!!!!! You hit it right on the money. That was the problem - I had the old
table with the same index names. After dropping this table, I was able to
successfully distribute the snapshot with a different destination name and
all indexes, primary keys, etc. were successfully transferred.

Thanks Raymond.

- Johnny

"Raymond Mak [MSFT]" wrote:

> 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
    ... constraints should be copied to the subscriber even though the ... destination object name is different than source object name. ... >> I am having trouble getting the indexes and primary keys copied to the ...
    (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)
  • Identity crisis: GUID VS. Range
    ... I'm developing a project in which I have several Sql Server Express and ... central Sql Server 2005 database. ... Assign ranges of primary keys in each subscriber in order to assign ...
    (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)
Quantcast