|
|
Posted by bobdurie@gmail.com on 09/15/07 00:16
On Sep 14, 5:33 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> bobdu...@gmail.com (bobdu...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
This advice was very helpful, thank you!!! I tested what you
suggested and works great. One final question. I'm concerned that
when i get my job up and running that it will not be very fault
tolerant. I followed the tips in the following paper:
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm#_Toc79339947
The steps all work, but the partitions that are being dropped/added
are always based on 'today'. If the job doesn't run for a few days,
i'm quite certain it will fail. Is there some way to query partition
function information in order to get the ranges so that multiple days
sliding can be done? Does this question even make sense? If it does,
let me know if you have any tips.
Thank you so much for your help!!!
Bob
[Back to original message]
|