How to send an email using SQL Server 2005

With SQL Server 2005, there is no need to use MAPI client to send emails. Fellow developers who have used MAPI in the previous versions of SQL Server are well aware of the challenges it had. However in Sql Server 2005, we can now use the Database Mail to send emails.

[Note: I assume you have set up and configured Database Mail. If not, check this link Database Mail Configuration Stored Procedures to use various stored procedures required to configure Database Mail. As an alternative, you could also use the SQL Server Surface area configuration tool to configure Database Mail]

Use the following script to send a mail from your Sql Server

USE [YourDB]
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'admin@xyz.com; xyz@xyz.com; pqr@xyz.com',
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;
GO


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

9 comments:

Anonymous said...

Thanks, dude - this was exactly what I was looking for! Much appreciated...

Chris

Unknown said...

Thanks a lot for this starter kit!

Paúl said...

Thanks for your help, it works perfect. But If you let me a little comment, one parameter is missing:

USE [YourDB]

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Your Profile',
@recipients = 'yourmail@yourdomain.com',
@body = 'Just testing the mail',
@subject = 'Sending Mail using Database Mail' ;

GO

@profile_name is the name of the profile that you've setup on DataBase Mail before try to sending
mails.

Hope this helps.

Once again thanks for your help and advice.

Makron

Suprotim Agarwal said...

Thanks for that input Paul.

Jay Edgar said...

Yeah, thanks!

Anonymous said...

Can Anyone Tell me "how to send an audit report generated by auditing tool( Which i am developing) to a mail address automatically after span of 1 month"

Suprotim Agarwal said...

You would have to create a job using SQL Server Management Studio or using T-SQL to send emails automatically after a given period of time.

Anonymous said...

I also want to know how make this run every day and send email alerts with the query results

Suprotim Agarwal said...

Priyab: Some links to help you out
http://support.microsoft.com/kb/908360

If you intend using a trigger
http://blog.netnerds.net/2008/02/create-a-basic-sql-server-2005-trigger-to-send-e-mail-alerts/