clearchannel
asked on
Stored Procedure to run every day at midnight
Hi guys
I sort of know how to write a stored procedure but IS there any way to get it to run every day at midnight on a shared platform where I not setup a scheduled task to actually run the SP?
What I want to do is delete all records from Classifieds where DateExpired > Date + 30.
Is it better to run this on the default.asp page someway and execute the SP before I select the latest records?
I am running on sql2005
I sort of know how to write a stored procedure but IS there any way to get it to run every day at midnight on a shared platform where I not setup a scheduled task to actually run the SP?
What I want to do is delete all records from Classifieds where DateExpired > Date + 30.
Is it better to run this on the default.asp page someway and execute the SP before I select the latest records?
I am running on sql2005
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
do you have access to the sql agant scheduling?
1. You can scehdule a sql job.
2. whenever you are accessing from asp page you need to ensure that job has run successfully otherwise call the stored procedure directly before proceeding.
2. whenever you are accessing from asp page you need to ensure that job has run successfully otherwise call the stored procedure directly before proceeding.
ASKER
I don't have access to the Agent only the following options under SQL2005.
Database
<my db name>
Security
logins
server roles
credentials
Server Objects
backup devices
Linked Servers
Triggers
Replication
Management
Activity Monitor
Database Mail
DTC
Database
<my db name>
Security
logins
server roles
credentials
Server Objects
backup devices
Linked Servers
Triggers
Replication
Management
Activity Monitor
Database Mail
DTC
Use task scheduler to add a job for SQLCMD. Check SQLCMD /? from the command line
SQLCMD <server> <login> <db> <sqlcommand>
SQLCMD <server> <login> <db> <sqlcommand>
ASKER
I do not have access to this sort of functionality.
If you read my message I am on shared SQL environment with a hosting company!
If you read my message I am on shared SQL environment with a hosting company!
Default.asp is your best bet then - but if users can bookmark other pages, then make it a global include page.
Make it perform a
delete .. classified .. where date..
on every single page that must not see old classifieds.
Make it perform a
delete .. classified .. where date..
on every single page that must not see old classifieds.
ASKER
Yeah but thats going to be processor intensive when I have over 1000 users acessing the site on a daily basis. I would rather run something once every night somehow.
is there no other way to do what I want?
is there no other way to do what I want?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Again, that relies on my pc being on the intenrnet at the time or even being powered up!
I don't know about ASP, but PHP code allows access to filesystems, so you can keep track using a status file. PHP doesn't mind checking many files for each page production, so I assume the same thing would work if ASP can manipulate files.
It's not really that intensive to fire a query on a properly indexed table. A page may run anywhere from 1-20 queries and still produce pages quite quickly.
It's not really that intensive to fire a query on a properly indexed table. A page may run anywhere from 1-20 queries and still produce pages quite quickly.
ASKER
I think I am going to execute a scheduled asp page to run.
BUT what is wrong with my code below?
SELECT * FROM dbo.Classifieds
WHERE (DateCreated+ TimePeriod + 30) >= Date()
BUT what is wrong with my code below?
SELECT * FROM dbo.Classifieds
WHERE (DateCreated+ TimePeriod + 30) >= Date()
SELECT * FROM dbo.Classifieds
WHERE (DateCreated+ TimePeriod + 30) >= GetDate()
assuming timeperiod is datetime
WHERE (DateCreated+ TimePeriod + 30) >= GetDate()
assuming timeperiod is datetime
Probably use functions:
SELECT * FROM dbo.Classifieds
WHERE DateAdd(d, 30, DateCreated+ TimePeriod) >= GetDate()
SELECT * FROM dbo.Classifieds
WHERE DateAdd(d, 30, DateCreated+ TimePeriod) >= GetDate()
ASKER
I have chaged the above to
"SELECT * FROM dbo.Classifieds WHERE DateAdd(d, 7, DateCreated+TimePeriod) < GetDate()"
Can someone please clarify that that call should select all records where the createdDate+timeperiod+7 < todays date
"SELECT * FROM dbo.Classifieds WHERE DateAdd(d, 7, DateCreated+TimePeriod) < GetDate()"
Can someone please clarify that that call should select all records where the createdDate+timeperiod+7 < todays date
you might be interested by the following:
http://technet.microsoft.com/en-us/library/ms187331.aspx
the WAITFOR would allow you to run at a given time...
now, you could "loop" to make it "scheduled" every week, or just run a script on your machine to connect once per day to run that...
http://technet.microsoft.com/en-us/library/ms187331.aspx
the WAITFOR would allow you to run at a given time...
now, you could "loop" to make it "scheduled" every week, or just run a script on your machine to connect once per day to run that...