Schedule a stored proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Schedule a stored proc

Author

Topic

jboles
Starting Member

1 Post

Posted - 2007-09-23 : 17:12:32

Overview:
This project is for a .net web app. The client (publication entity) is charging customers by the character for posting stories to his website. There are 3 character tiers. 2000, 5000, 12000. When the customer has exceeded their character limit they may continue to submit stories but will be charged a "over limit" fee. at the beginning of each month the character limits are reset. I'm using SQL server 2005 on the backend and have a cutomer account table that contains the customers character limit, characters used and characters over.

The problem:
I'm having difficulty figuring out how to reset the character limits every month. The Sql Server I'm using is a web-based version that does not include the many benefits of Enterprise Manager. Therefore, scheduling a stored procedure that will reset the character limits at 12:00AM on the first day of every month won't be an option. I'm guessing that my solution is as follows:
1. user submits a new story
2. via stored procedure, I determine first if today is equal to the first day of (a given) month at exactly 12:00am.
3. If yes to above than reset all customer accounts to thier original character limits
4. If not proceed with other logic for managing customer character balances.

Summary:
I basically need to figure out how to automate a stored procedure that will restore the character limits on the first day of every month... without using Enterprise Manager, or I need to figure out a way to code the stored proc according to step 2. above.

Any help is GREATLY appreciated as I'm up against a tight release date with this customer.

Thanks!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-23 : 17:15:58

You can schedule os job to run the sp with oqsl.