Indexes and keys are not synced w/ snapshot publication

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
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
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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: