Schedule a SQL statement to run from SQL Server ?

How can I schedule a SQL Statement to run from SQL Server say every day at 4
pm ?
For example, I would like to run the following sql statement every day at 4
pm:
"delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
it to run automatically on SQL server ?
Thanks.Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fniles" <fniles@.pfmail.com> wrote in message news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at 4 pm ?
> For example, I would like to run the following sql statement every day at 4 pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule it to run automatically
> on SQL server ?
> Thanks.
>|||"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
Create a new job in the SQL Server Agent, where it runs your T-SQL
statement. Then schedule it to run every 4 hours (see the Schedules tab).
Steve|||Have you looked at Management / SQL Server Agent / Jobs?
Also, since other things can be running on the server, and your job won't
always run at *precisely* 4:00, and if precision in the amount of data you
delete is important, you may want to say:
DECLARE @.yesterday400 SMALLDATETIME
SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
GETDATE())))
DELETE myTable WHERE myCol < @.yesterday400
(This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
opposed to the time the statement is executed, which could be a few seconds
off.)
You should also consider putting your DELETE statement in a stored
procedure, and calling the stored procedure from the job.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> How can I schedule a SQL Statement to run from SQL Server say every day at
4
> pm ?
> For example, I would like to run the following sql statement every day at
4
> pm:
> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I schedule
> it to run automatically on SQL server ?
> Thanks.
>|||Thank you all for your replies.
In Enterprise Manager, I do not see Management or SQL Server Agent.
I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
run. Is this what you all meant ?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every day
>> at 4 pm ?
>> For example, I would like to run the following sql statement every day at
>> 4 pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule it to run automatically on SQL server ?
>> Thanks.
>|||I think you're looking in the wrong place.
http://www.aspfaq.com/img/2403.gif
--
http://www.aspfaq.com/
(Reverse address to reply.)
"fniles" <fniles@.pfmail.com> wrote in message
news:#Si9kYcGFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Thank you all for your replies.
> In Enterprise Manager, I do not see Management or SQL Server Agent.
> I do see under "Tools" - "Job Scheduling" where I can schedule a T-SQL to
> run. Is this what you all meant ?
> Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23LOVUPcGFHA.2356@.TK2MSFTNGP12.phx.gbl...
> > Create an SQL Server Agent job. EM, Management, SQL Server Agent, Jobs.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "fniles" <fniles@.pfmail.com> wrote in message
> > news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
> >> How can I schedule a SQL Statement to run from SQL Server say every day
> >> at 4 pm ?
> >> For example, I would like to run the following sql statement every day
at
> >> 4 pm:
> >> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
> >> schedule it to run automatically on SQL server ?
> >>
> >> Thanks.
> >>
> >
> >
>|||Thank you all for your replies.
I found the Management / SQL Server Agent / Jobs.
When I went there, and click "Jobs", I did see the job that I created
earlier using the menu "Tools" - "Job Scheduling".
So, creating a job thru menu "Tools" - "Job Scheduling" and thru Management
/ SQL Server Agent / Jobs are the same, right ?
Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u8FBAVcGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job won't
> always run at *precisely* 4:00, and if precision in the amount of data you
> delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY, 0,
> GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00 PM, as
> opposed to the time the statement is executed, which could be a few
> seconds
> off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every day
>> at
> 4
>> pm ?
>> For example, I would like to run the following sql statement every day at
> 4
>> pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule
>> it to run automatically on SQL server ?
>> Thanks.
>>
>|||> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
Management
> / SQL Server Agent / Jobs are the same, right ?
To be honest, I've never done the former, so I can't guarantee that they
produce identical results.
I think I prefer the latter because you can visually see which server is
creating the job.
A|||Thanks.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OZ1AtTdGFHA.2356@.TK2MSFTNGP12.phx.gbl...
>> So, creating a job thru menu "Tools" - "Job Scheduling" and thru
> Management
>> / SQL Server Agent / Jobs are the same, right ?
> To be honest, I've never done the former, so I can't guarantee that they
> produce identical results.
> I think I prefer the latter because you can visually see which server is
> creating the job.
> A
>|||Aaron,
Just for my curriosity - What would be the adavantage of putting the DELETE
statement in a stored proc? I know that a Stored Proc often performs a bit
faster and it's good practice to create stored proc's for recurring scripts,
but is there any specific reason here that I'm missing?
Regards
Steen
Aaron [SQL Server MVP] wrote:
> Have you looked at Management / SQL Server Agent / Jobs?
> Also, since other things can be running on the server, and your job
> won't always run at *precisely* 4:00, and if precision in the amount
> of data you delete is important, you may want to say:
> DECLARE @.yesterday400 SMALLDATETIME
> SET @.yesterday400 = DATEADD(HOUR, 16, DATEADD(DAY, -1, DATEDIFF(DAY,
> 0, GETDATE())))
> DELETE myTable WHERE myCol < @.yesterday400
> (This will make sure the cutoff is always yesterday at exactly 4:00
> PM, as opposed to the time the statement is executed, which could be
> a few seconds off.)
> You should also consider putting your DELETE statement in a stored
> procedure, and calling the stored procedure from the job.
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O9n50NcGFHA.2280@.TK2MSFTNGP15.phx.gbl...
>> How can I schedule a SQL Statement to run from SQL Server say every
>> day at 4 pm ?
>> For example, I would like to run the following sql statement every
>> day at 4 pm:
>> "delete myTable where myCol < dateadd(day, -1, getdate())". Can I
>> schedule it to run automatically on SQL server ?
>> Thanks.|||> Just for my curriosity - What would be the adavantage of putting the
DELETE
> statement in a stored proc? I know that a Stored Proc often performs a bit
> faster and it's good practice to create stored proc's for recurring
scripts,
> but is there any specific reason here that I'm missing?
- Maintainability, encapsulation
- easy ability to call the DELETE independent of the job
IMHO, all SQL statements should be in stored procedures.