Indexes: changing/adding with Publication enabled

Are there any stored procedures available to alter indexes while Publication
is enabled?
What is sp_addscriptexec used for?
thank you,
bob
Dropping and creating indexes on the publisher is permited but not
replicated. To propagate this to the subscriber, creating a TSQL script and
running sp_addscriptexec is the way to go. The same thing could be achieved
by using linked servers if they were all online but using sp_addscriptexec
is easier and will make sure the script is applied to all subscribers when
they synchronize.
hth,
Paul Ibison
|||If I wanted to take the "long" way without utilizing sp_addscriptexec, would
this approach be valid:
- exec TSQL script on the publisher
- remote connect to subscribers and execute TSQL script
(2) I would not have to delete subscriptions or publications or disable
publishing in the above scenario?
Thanks Paul.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eV$$6kAdEHA.3616@.TK2MSFTNGP10.phx.gbl...
> Dropping and creating indexes on the publisher is permited but not
> replicated. To propagate this to the subscriber, creating a TSQL script
and
> running sp_addscriptexec is the way to go. The same thing could be
achieved
> by using linked servers if they were all online but using sp_addscriptexec
> is easier and will make sure the script is applied to all subscribers when
> they synchronize.
> hth,
> Paul Ibison
>
|||Robert,
yes - this works OK. Adding an index isn't treated as a table change in the
same way as adding a column, and is permitted on the publisher and
subscriber without affecting the replication setup.
Regards,
Paul Ibison

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: