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...[
vbcol=seagreen]
> 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.
>[/vbcol]|||"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...
>|||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...
at[vbcol=seagreen]
>|||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...
> 4
> 4
>|||> 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...
> Management
> 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:[vbcol=seagreen]
> 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...