Is there a way to make a stored proc automatically execute at a certain time of the day?
Sorry, SQL 7 DB
You can schedule a "Job" to run at a particular time. Try looking them up in Books Online.
Use the SQL Server Agent to execute a query or cmd file. You can even have email alerts sent to you if it fails.