woodan1
asked on
sql server procedure to run at preset time and day
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
No problem.
Make sure that the IT department also has given the SQL Agent adequate permissions to run the job.
Make sure that the IT department also has given the SQL Agent adequate permissions to run the job.
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.