469,946 Members | 1,805 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

Kick off Stored Procedure to run Nightly?

31
I have a stored procedure that updates a table. I want that table to be updated nightly. I looked into the DTS package but it seems a bit difficult. Is there some other schedule in Enterprise that I can kick off my stored proc without having to build a DTS package?

In DTS, they ask for all of these symbols and want the query, but my code is already in stored proc form.

Help?
Sep 10 '07 #1
4 4181
rob313
16
What you need to do is use SQL Agent. You can schedule the job to run nightly at your prferred time. When you create a new SQL Agent job, you then ad a job step. In this job step you specify the SQL command you want to run. I'm assuming you are using SQL Server 2000 since you refer to DTS. Using SQL Server Enterprise manager, expand the SQL Server you are working with, then expand the 'Managment' tree, then expand 'SQL Agent' tree and right click on Jobs and choose New Job... The tabs here should be self explanatory.
Sep 10 '07 #2
Joell
31
Ok I see where the job steps are. In the command window, I can type in my proc name sp_DailyOrders and it will know how to kick it off?
Sep 10 '07 #3
rob313
16
In the step name tab, just give the step a name like 'Execute procedure', make sure the database is the correct database where your procedure lives, and type in exec sp_your_proc_name in the command section. Then move to the Scheduile tab and click the button to add a new schedule and choose the frequency etc that you want the job to tun. If you have operators setup on your server, then you can use the notification tab to have the job email you when it completes or fails.
Sep 10 '07 #4
Joell
31
Thanks soooooooo much! You are awesome!

Jo
Sep 11 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Rhino | last post: by
8 posts views Thread by Thomasb | last post: by
2 posts views Thread by Dino L. | last post: by
3 posts views Thread by kd | last post: by
reply views Thread by jer006 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.