HOw to Send Email

Step 1

Step 2

Expand the Management tab, then select SQL Server Logs, then right-click on Database Mail, then select Configure Database Mail.

Step 3

The following window appears:

Click Next.

Step 4

Select the Set up Database Mail by performing the following tasks: radio button.

Step 5

Click Next, then a confirmation box appears; click OK on that.

Step 6

When you click OK, then write the profile name description and click on Add.

Step 7

Then, a new window appears where you provide your profile name and configure Outgoing Mail Server(SMTP) then click Basic authentication and provide your email id and password then click OK.

Step 8

Then in the new window, it will show your profile, check that and click Next.

Step 9

Then in the new window, the Configure System Parameters description is shown.

Step 10

Click Next. It will show a confirmation about your profile; click Finish.

Step 11

Now the email service has been configured, click Close.

Step 12

Send a test email by right-clicking on database mail and click Send Test E-Mail.

Step 13

Provide the Test Email Id and click On Send Test E-Mail. Then check your mail to see if you got the mail. Enjoy!!

Note

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter using the sp_configure Stored Procedure, as shown here:

sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE 'Database Mail XPs', 1 GO RECONFIGURE GO

Output Message

The Configuration option "show advanced options" changed from 0 to 1. Run the RECONFIGURE statement to install.

The configuration option "Database Mail XPs" changed from 1 to 1. Run the RECONFIGURE statement to install.

Use the following query to send mail using query:

Send Mail Query

USE msdb;--Must Use msdb database, otherwise you will get error.

GO EXEC sp_send_dbmail @profile_name='MyTestMail', @recipients='[email protected]', @subject='My Test Mail Service.', @body='Database Mail Received Successfully.'

This is the body of this message.

Note

If you are using a database other than msdb, then use a stored procedure like msdb.dbo.sp_send_dbmail. The log can be checked in sysmail_log table as shown below:

SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO select * from sysmail_log

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra10y ago
Accepted answer
You can implement SQLServer job which will run interval basis. And it will send the mail with attachment. The mail configuration you need to do in sqlserver. Please follow below links:

https://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/

https://blogs.msdn.microsoft.com/sqlagent/2010/11/10/sql-database-mail-send-emails-with-attachment/

https://www.simple-talk.com/blogs/sending-query-results-to-excel-through-e-mail/

http://www.kodyaz.com/t-sql/send-email-with-attachment-in-sql-server.aspx
+2
snowal raj10y ago
Thanks For Reply,

i need send mail with attachment .that should be query results in micrisoft excel .
0
Pradeep Sahoo10y ago
Use SQL Jobs to send email , That is the easy way .

Please follow the below link :

http://stackoverflow.com/questions/29645387/send-by-mail-a-query-result-with-a-job-in-sql-server
0
Ananth G10y ago
Step 1
Log in to SQL Server 2008 with a correct user name and password.
Step 2
Expand the Management tab, then select SQL Server Logs, then right-click on Database Mail, then select Configure Database Mail.
Step 3
The following window appears:
Click Next.
Step 4
Select the Set up Database Mail by performing the following tasks: radio button.
Step 5
Click Next, then a confirmation box appears; click OK on that.
Step 6
When you click OK, then write the profile name description and click on Add.
Step 7
Then, a new window appears where you provide your profile name and configure Outgoing Mail Server(SMTP) then click Basic authentication and provide your email id and password then click OK.
Step 8
Then in the new window, it will show your profile, check that and click Next.
Step 9
Then in the new window, the Configure System Parameters description is shown.
Step 10
Click Next. It will show a confirmation about your profile; click Finish.
Step 11
Now the email service has been configured, click Close.
Step 12
Send a test email by right-clicking on database mail and click Send Test E-Mail.
Step 13
Provide the Test Email Id and click On Send Test E-Mail. Then check your mail to see if you got the mail. Enjoy!!
Note
After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter using the sp_configure Stored Procedure, as shown here:
```
sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE 'Database Mail XPs', 1 GO RECONFIGURE GO 
```
Output Message
The Configuration option "show advanced options" changed from 0 to 1. Run the RECONFIGURE statement to install.
The configuration option "Database Mail XPs" changed from 1 to 1. Run the RECONFIGURE statement to install.
Use the following query to send mail using query:
Send Mail Query
USE msdb;--Must Use msdb database, otherwise you will get error.
```
GO EXEC sp_send_dbmail @profile_name='MyTestMail', @recipients='[email protected]', @subject='My Test Mail Service.', @body='Database Mail Received Successfully.' 
```
This is the body of this message.
Note
If you are using a database other than msdb, then use a stored procedure like msdb.dbo.sp_send_dbmail. The log can be checked in sysmail_log table as shown below:
```
SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO select * from sysmail_log
```
+1