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

Posted on 2010-01-07
Medium Priority
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
  • 2
  • 2
LVL 11

Expert Comment

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

Expert Comment

ID: 26201321
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

ID: 26202031
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.

Accepted Solution

SQLTriVegg earned 2000 total points
ID: 26279935
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

ID: 26295434
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

569 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