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

Posted on 2010-01-07
Last Modified: 2012-05-08
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

Question by:dunkin1969
    LVL 11

    Expert Comment

    You can create sql agent job and set notifications to send email.
    schedule the job.
    LVL 11

    Expert Comment

    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

    Author Comment

    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.
    LVL 3

    Accepted Solution

    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.

    Author Comment

    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.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now