Friday, May 9, 2014

Query to Send Mail in SQL Server 2012

This is an example query how to send mail using SQL Server 2012. Before try this, you must set your profil default of mail server that used as sender in SQL Server (you can read about this here).

EXEC msdb.dbo.sp_send_dbmail       
        @recipients='thoriq.aziz@technosoft.co.id',   -- recipient of mail 
        @subject = 'MAKAN',                           -- subject mail
        @body = 'Content',                            -- content mail
        @body_format = 'HTML' ;

Thursday, May 8, 2014

Configure Microsoft Exchange Online (Office 365) in SQL Server 2012

Today, I faced a new problem about email configuration in SQL Server 2012. I wanna send an email using Stored Procedure in SQL Server. Actually, it’s not difficult if you are using hotmail, gmail, yahoo, or others mail that known their smtp server. I said a problem because I used a Microsoft Exchange mail. Tried searching found that many people also got same problem and didn’t know the solution. Finally, I got new way from this link. But, I didn’t follow all the instructions because I don’t use windows server in my laptop. So, I just followed until step 5. If you get  same problem, hope this steps help you.
  1. Login to your Microsoft Exchange Account in office365.com
  2. Click Setting > Option. Screenshot (4)
  3. At the bottom click Setting for POP or IMAP access… option.
  4. In the Setting for POP or IMAP Access Window, look at SMTP setting. Remember this Server name and Port, because it used to configure in SQL Server later. Screenshot (5)
  5. Open Microsofft SQL Server. Expand Management, right click Database Mail and click Configure Database Mail. Screenshot (6)
  6. Database Mail Configuration will displayed, fill the Profile name and Description textbox, then click Add > New Account. Screenshot (7)
  7. Create a new account using your Microsoft Exchange account. Fill the Server name and Port number with Server name and Port got before and click OK. Screenshot (8)
  8. Last, Set this Profile Mail as default by right click Database Mail and choose Manage profile security. Screenshot (9)
  9. Now, you can test this configuration success or not by Send Test E-Mail that shown when you right click in Database Mail.