Sql server Database Mail

SQL Server:

 

Sending Mail:

To give you the ability to send an email from a database, Transact-SQL provides the sp_send_dbmail stored procedure.

This store procedure is created in the msdb database.

 

        EXEC msdb.dbo.sp_send_dbmail

        @profile_name = N'Central Administrator',

        @recipients = N'jaywiler@hothothot.net',

        @body = N'The Persons table has received a new record.',

        @subject = N'New Record';

        GO

The profile_name value must hold a valid for an existing profile. If you omit this argument, the database engine uses the current user or a default profile created in the msdb database. If there is no default profile, you must create one. To do this, in the Object Explorer, expand the Management node and double-click Database Mail

Before executing this procedure, you should check the security settings on your server. Otherwise you may receive an error. Here is an example:

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0

SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database 

Mail XPs' because this component is turned off as part of the security configuration 

for this server. A system administrator can enable the use of 'Database Mail XPs' 

by using sp_configure. For more information about enabling 'Database Mail XPs', 

see "Surface Area Configuration" in SQL Server Books Online.

To solve this problem, open a Query window and type the following:

sp_configure N'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure N'Database Mail XPs', 1;

GO

RECONFIGURE;

GO

 

 

 

 

Database Mail stores all sent email in the msdb database. You can examine them by running the following query

 

USE msdb

SELECT * FROM sysmail_allitems

ORDER BY mailitem_id DESC