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 Enabled or Disabled?

Hey folks,
SQL Server 2000 SP3/4
How can I determine if an index is enabled or disabled? The information must
be kept somewhere, but I can't seem to find it. Not in sysindexes,
information_schema, sp_helpindex.
Easy to get in SQL 2005 mind you.
--
Thanks,
Scott H.Well, it's been a while, but I don't think you can disable an index on SQL
Server 2000, can you?
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:D12CF217-6C70-4483-B07A-3E00F0A475AA@.microsoft.com...
> Hey folks,
> SQL Server 2000 SP3/4
> How can I determine if an index is enabled or disabled? The information
> must
> be kept somewhere, but I can't seem to find it. Not in sysindexes,
> information_schema, sp_helpindex.
> Easy to get in SQL 2005 mind you.
>
> --
> Thanks,
> Scott H.|||> Well, it's been a while, but I don't think you can disable an index on SQL
> Server 2000, can you?
Nope. That ability was introduced in SQL Server 2005.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uFrZOVhqIHA.552@.TK2MSFTNGP06.phx.gbl...
> Well, it's been a while, but I don't think you can disable an index on SQL
> Server 2000, can you?
>
>
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:D12CF217-6C70-4483-B07A-3E00F0A475AA@.microsoft.com...
>> Hey folks,
>> SQL Server 2000 SP3/4
>> How can I determine if an index is enabled or disabled? The information
>> must
>> be kept somewhere, but I can't seem to find it. Not in sysindexes,
>> information_schema, sp_helpindex.
>> Easy to get in SQL 2005 mind you.
>>
>> --
>> Thanks,
>> Scott H.
>|||Ok, that's what I thought. So that makes it easy.
SELECT *, Enabled = 1
FROM sysindexes;
:-)
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OOgQMfhqIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Well, it's been a while, but I don't think you can disable an index on
>> SQL Server 2000, can you?
> Nope. That ability was introduced in SQL Server 2005.
>|||On Tue, 29 Apr 2008 12:21:43 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>Well, it's been a while, but I don't think you can disable an index on SQL
>Server 2000, can you?
Doesn't look that way from the docs. ALTER INDEX had not been
introduced yet.
Roy Harvey
Beacon Falls, CT|||Good thinking, Aaron!
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZZRWhhqIHA.4476@.TK2MSFTNGP04.phx.gbl...
> Ok, that's what I thought. So that makes it easy.
> SELECT *, Enabled = 1
> FROM sysindexes;
> :-)
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:OOgQMfhqIHA.2292@.TK2MSFTNGP03.phx.gbl...
>> Well, it's been a while, but I don't think you can disable an index on SQL Server 2000, can you?
>> Nope. That ability was introduced in SQL Server 2005.
>>
>