|
|
Posted by Erland Sommarskog on 09/14/07 21:33
bobdurie@gmail.com (bobdurie@gmail.com) writes:
> 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.
This is a fair game for dynamic SQL. But first let me change how
you compute @today a bit:
declare @today as char (8);
select @today = convert(char(8),
dateadd(dy, datepart(dy, current_timestamp) - 1, @yearstart), 112)
This give you the format YYYYMMDD which is never subject to different
interpretations due to dateformat or language settings.
The dynamic SQL is simple enough:
EXEC('alter table [partitionedlogs-staging]
with check
add constraint after ' + @today ' + '
check ([logdate] >= ' @today ' + '
and [logdate] < dateadd(dy, 1, ' + @today + '))')
Note here that I also added the date to the constraint name, as the
constraint name must be unique in the schema.
Now that you have seen dynamic SQL in action, you are likely to use it
all over town. Permit me therefore to point out that while this is a
useful feature, it also lends it to all sorts of abuse, and I have a
long article on dynamic SQL on my web site that you should read before
you start to use it wildly: http://www.sommarskog.se/dynamic_sql.html.
> 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.
Yes, you can run jobs from SQL Server Agent. You find it in the Object
Explorer in the bottom of the tree for the server.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|