how to schedule SQL Stored Procedures and email once the procedures are complete?

Hello,

I have two sql procedures that I need to execture on a daily basis. How can I have SQL Server schedule them at a set time and then send an email out to appropriate groups with the time it took to execute both procedures?

I am using SQL Server 2008 R2.

Thanks!
dbansal1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sreedhar2uCommented:
dbansal1Author Commented:
sreedhar2u,

there is no tools, job scheduling (step 6).  This article was written in 1999, perhaps it was made for SQL Server 2000.  

Thanks
wittysloganCommented:
would a sql agent job do the trick?
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

wittysloganCommented:
do you have management studio as part of your sql package?
dbansal1Author Commented:
wittslogan,

I do have the management studio.  How do I schedule an sql ajent job?
Auric1983Commented:
http://msdn.microsoft.com/en-us/library/ms191439.aspx

You can have it call the stored procedure, as well as other things like Email notifications etc. etc.
wittysloganCommented:
great:

on management studio go to servers and expand the folders and you should see one called:
Sql agent

Expand this and you should see one called jobs.
Right click this and click on "new Job"
in the general bit You'll be asked for a title and I always change the job owner to sa.
Then go to steps:  
Click on new and in the large white area copy the name of the stored proc plus any parameters you may need.
In the drop down menu click on the database you need to run this on.
go to advanced and choose an output file to write the log to.
IF you have 2 procs then create a second step.
Then go to schedule and create your schedule.

now in order to get start and end time I suppose the simplest thing to do is on step one, before the stored proc write
SELECT GETDATE()
GO
and at the end of step 2
after the stored proc write:
SELECT GETDATE()
GO

and then generate and email containging the file path
so a step 3
with the email on it.

Try this let me know if you need more of a pointer.






Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbansal1Author Commented:
wittslogan,

i am having trouble with the SQL Server Agent.

It says on the bottom of my server SQL Server Agend (Agend XPs disabled).

TITLE: Microsoft SQL Server Management Studio
------------------------------

Unable to start service SQLAGENT$r2stand on server MSFIXED. (mscorlib)

------------------------------
ADDITIONAL INFORMATION:

The SQLAGENT$r2stand service on MSFIXED started and then stopped. (ObjectExplorer)

------------------------------
BUTTONS:

OK
------------------------------

What can I do??
wittysloganCommented:
your sql agent is stopped.

You need to go to the server and then
Start -> Control Panel -> Administrative Tools -> Services
Find SQL Server (MSSQLSERVER) right click and press start.

Then you should be okay to proceed
dbansal1Author Commented:
The reason my sql agent was not starting was because it was running as a network service. I chagned it to local service and it works now.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.