2005 Table Partitioning - Next Used FG

Hi - I want to set the next used filegroup for a partition scheme
dynamically - ideally like this:
ALTER PARTITION SCHEME DatePScheme NEXT USED @.dailyPartitionsFGName
But SQL Server seems unimpressed by this syntax... so is this possible or
do I just have to use Dynamic SQL'
Thanks!!
Mike> ALTER PARTITION SCHEME DatePScheme NEXT USED @.dailyPartitionsFGName
> But SQL Server seems unimpressed by this syntax... so is this possible or
> do I just have to use Dynamic SQL'
You'll need to either use dynamic SQL or SQLCMD script variable
substitution. SQLCMD script example:
:SETVAR dailyPartitionsFGName MyDailyFileGroup
ALTER PARTITION SCHEME DatePScheme NEXT USED $(dailyPartitionsFGName)
Hope this helps.
Dan Guzman
SQL Server MVP
"Michael Knee" <mike__k@.hotmail.com> wrote in message
news:%23t%23arI2rHHA.4676@.TK2MSFTNGP03.phx.gbl...
> Hi - I want to set the next used filegroup for a partition scheme
> dynamically - ideally like this:
> ALTER PARTITION SCHEME DatePScheme NEXT USED @.dailyPartitionsFGName
> But SQL Server seems unimpressed by this syntax... so is this possible or
> do I just have to use Dynamic SQL'
> Thanks!!
> Mike
>