Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


sql server procedure to run at preset time and day

Posted on 2005-03-28
Medium Priority
Last Modified: 2006-11-18
I am interested to find out if there is a way to run a procedure within sql server or other on Fridays at 12:00pm without my interface.

I have a time keeping system that users enter their time weekly into.  What I need is a way for the system to automatically run a query to see who didn't enter their time or +/- a preset value(servers always on).  I have the query.  It would then need to send an preset body email notifying them to enter their time by the end of the day.  This would have to be a loop since it could be from 0 to many users which I have in vb(msaccess) code.

I could do this manually from within Access by running vb code to email the users, but I want a more automated way of doing it without me or anyone else having to spawn the check.

Anyone have any ideas...if there is a way to do it in sql server, msaccess, vb or other I would be interested.

I think this question is generic enough that you won't need any specifics(table/field names).  I just need a basic model or idea and, hopefully, I could take it from there and fill in the rest.

Question by:woodan1
  • 3
LVL 27

Accepted Solution

ptjcb earned 500 total points
ID: 13646934
YOu could set up a job and schedule it.

In EM - look under Management - SQL Agent - Jobs.

LVL 27

Expert Comment

ID: 13646946
From BOL:

How to create a job (Enterprise Manager)
To create a job

Expand a server group, and then expand a server.

Expand Management, and then expand SQL Server Agent.

Right-click Jobs, and then click New Job.

In the Name box, enter a name for the job.

Clear the Enabled check box if you do not want the job to be run immediately following its creation. For example, if you want to test a job before it is scheduled to run, disable the job.

Under Source, do one of the following:
Click Target local server if the job should run on this server only. Skip to Step 9 if you select this option.

Click Target multiple servers if the job should run on other servers. Then click Change.
This option is enabled only if the server is a master server.

In the Change Job Target Servers dialog box, on the Available Servers tab, click a server, and then click the right arrow to move the server to the Selected target servers list.

Click OK to return to the New Job Properties dialog box.

In the Owner list, select a user to be the owner of the job.

In the Description box, enter a description of what the job does. The maximum number of characters is 512.

Important  Each job must have at least one step. A step must be created with the job before the job can be saved. For more information, see Creating Job Steps.


Author Comment

ID: 13647294
I see how to do it, but when I went to do it the wizard said I didn't have permissions.  I'm assuming that our IT dept will be able to solve this issue and I will be able to proceed.

My sql server book mentions nothing of this capability.  Thanks for letting me know.  If I get this up and running, I may go crazy with it...I can see a lot of possibilities.

A dictionary is no good if you don't know how to start to spell the word.  Thanks.
LVL 27

Expert Comment

ID: 13647516
No problem.

Make sure that the IT department also has given the SQL Agent adequate permissions to run the job.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question