Hi,

I'm trying to create a bunch of scripts that will automatically roll
the partitions of one of my database tables. The partition scheme can
be arbitrary, but for my purposes i'm testing with a scheme that
breaks on "days", has 5 partitions, and every day would cycle out the
oldest, and bring in a new one.

Question #1:
I've got the scripts to do the rolling generic enough, except for when
i'm adding a CONSTRAINT to the staging table. Here's what i want to
do:


-- get a date var for today with time cleared
declare @year as nchar(4);
select @year = datename(year, current_timestamp);
declare @yearstart as nchar(10);
select @yearstart = @year + N'0101';
declare @today as nchar (64);
select @today = dateadd(dy, datepart(dy, current_timestamp) - 1,
@yearstart);
--select @today;

-- set the date period to be two days from now
alter table [partitionedlogs-staging]
with check
add constraint aftertoday
check ([logdate] >= @today
and [logdate] < dateadd(dy, 1, @today))
go


Of course, this doesn't work because i think its trying to make the
constraint variable:
Variables are not allowed in the ALTER TABLE statement.

But in reality, i want that date to be FIXED at the time of writing
the constraint. I can programattically generate the script with fixed
values, but if i can avoid that it would preferred. Can this be done
with stored procedures perhaps?

Question #2
Perhaps of more importance, is how i actually automate running of
these scripts on a daily basis. Does sql server have built in
scheduled tasks or running of scripts to facilate this? I haven't
found any.

I am by no means a DBA nor a sql server novice, but i have managed to
do this process manually. I only need to POC this to determine if it
is possible. Any or guidance that can be offerred would be most
appreciated.

Thanks!

Bob