Moving a clustered index to a different partition scheme

> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
script instead of graphical tools to make major database changes so that you
can see exactly what actions are being made to implement the change.
You can workaround the error by selecting Index-->Properties-->Storage and
then click the script button to generate the clustered index create script
to a new window. Then change the partition scheme as desired and add
"DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is the
most efficient way to move the clustered index since it avoids the sort.
Hope this helps.
Dan Guzman
SQL Server MVP
<randykilmer@.hotmail.com> wrote in message
news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
> Hello,
> I am trying to use SSMS to move a clusted index to a new partitioned
> scheme. I have done everything msdn said to do:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/
> 167ebe77-487d-4ca8-9452-4b2c7d5cb96e.htm
> When I change the partition scheme in the index properties (storage
> tab) and hit ok or try to script it I get this error:
> You cannot perform operation Remove on an object in state Existing.
> (Microsoft.SqlServer.Smo)
> I can not find this error anywhere.
> We are trying to move the clustered index from a partitioned scheme on
> a small sized drive to a new partitioned scheme on a larger drive.
> Hoping this is will keep the partitions and move the data quickly, as
> it a large table.
> Any help would be greatly appreciated,
> Randy
> The error looks like a SMO/SSMS bug to me.
I submitted feedback on this along with the workaround:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=319924
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:C3E3DEFA-79E3-40A5-9FC4-44D7C3C57A75@.microsoft.com...
> The error looks like a SMO/SSMS bug to me. In any case, I suggest you use
> script instead of graphical tools to make major database changes so that
> you can see exactly what actions are being made to implement the change.
> You can workaround the error by selecting Index-->Properties-->Storage and
> then click the script button to generate the clustered index create script
> to a new window. Then change the partition scheme as desired and add
> "DROP_EXISTING = ON" to the WITH clause. CREATE...WITH DROP_EXISTING is
> the most efficient way to move the clustered index since it avoids the
> sort.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <randykilmer@.hotmail.com> wrote in message
> news:af9bd28f-0594-4cd8-8033-e708a35666f6@.a35g2000prf.googlegroups.com...
>