October 19, 2016 at 12:34 pm
In a sql server 2012 database, I need to send out email messages to selected users when error records are located from a specific query. I also need to send the results of the query to the users. Due to this fact, I have the following questions to:
1. Is it better to send out the email messages using the database mail on sql server and/or the office 365 live outlook mail? Would you also tell me why one mail method is better than a different mail method?
2. Would you show me the following about using the database mail on sql server:
a. How to setup the database mail,
b. How to attach the query results obtained to the email message,
c. How to 'connect' to the database mail when a daily scheduled job finds it needs to email the results
to various users?
October 20, 2016 at 12:52 pm
Hi Diane, thought I would take a stab at answering some of your questions...
1. Is it better to send out the email messages using the database mail on sql server and/or the office 365 live outlook mail? Would you also tell me why one mail method is better than a different mail method? I can only comment on database mail. I have used it for years and once it is set up, it usually does not have any issues and is reliable unless the entire sql instance is down.
2. Would you show me the following about using the database mail on sql server:
a. How to setup the database mail - there are a few steps to this. We always use the Database Mail wizard in SSMS to do this. The key to setting it up is knowing your mail server name and ensure that the port you specify is open (default is 25). You create an DB Mail account and a profile. Your best bet is to google "Step by Step setting up Sql Server Database Mail" and see the screenshots people have out there and follow those.
b. How to attach the query results obtained to the email message - once you get DB mail working, you can follow these steps - https://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/
c. How to 'connect' to the database mail when a daily scheduled job finds it needs to email the results
to various users? This is done automatically and can be scheduled inside the Sql job. For example, you could have a job step which emails the results of a query to a mail group. This is all confgured in the sql job. The link above should help with that.
October 20, 2016 at 9:11 pm
I think the only thing you're missing is the URL to Books Online for the procedure you're going to use to send the email. It's https://msdn.microsoft.com/en-us/library/ms190307(v=sql.110).aspx. It includes information on how to run a query and attach it.
I've also used it for years and have found it to be very reliable. If I were doing this, I would write a procedure to do the work you need done. One part of it would send the email using the sp_send_dbmail stored procedure. Then, simply create a database job to execute the procedure.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply