• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

*** How to Schedule, Execute, and Export a SQL statement in SQL Server 2005

Hello Experts,
I have to run a query every morning and email the results to a few people.  Is there a way to automate this?  I am using SQL Server Management Studio in SQL Serve 2005.

The steps are:
1- Open query
2 - execute query
3 - export results to a file (or copy/paste results to email body)
4 - email recipients

Thanks!!
0
dunkin1969
Asked:
dunkin1969
  • 2
  • 2
1 Solution
 
rajvjaCommented:
You can create sql agent job and set notifications to send email.
schedule the job.
0
 
rajvjaCommented:
Steps:
1. Right click on Sql Agent and create new job
2. Create step.
3. Paste the query in the corresponding textbox.
4. Select schedule the job and set the schedule settings
5. Click notifications and set the options. Email -> if job fails bla bla
0
 
dunkin1969Author Commented:
Thank you rajvja.  I am trying this now.
It looks like my option to email the results is grayed out.. so I am checking to see why.
0
 
SQLTriVeggCommented:
I believe the solution to use "notifications" in the SQL Job will only email notifications of the job outcome, that is, whether it failed or succeeded.

If you need to email the results of a query, then you can use sp_send_dbmail.  First you need to configure DB Mail which is very easy to do if you have the address of your SMTP server.

Once that is configured, then sp_send_dbmail has several parameters, two of which are @query and @recipients (described well in BOL) where you simply pass in the query and email address.  Save all this in a stored proc, then schedule the proc to be run from a SQL Job.
0
 
dunkin1969Author Commented:
SQLTriVegg,
Yes, it looks like even though SQL Server Agent can execute a SQL command,  it doesn't provide a vehicle for emailing the actual results to a recipient.  Liek you said, the "notifications" are referring to the job outcome... whether it was a success or failure.

Thanks for the tip.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now