Email from SQL Server

Email from SQL Server

Being a developer, we understand that sending Email communications from application is of how much importance. As the applications are moving towards dynamic era, developers are putting every bit of their knowledge and creativity to make them so.

There is an easier way to send Emails from applications itself (Using IIS) but it sometimes does not work or suits the requirements because of the limitations. Another one is using third party tools / DLLs which works well. But the problem with these is that good programs do not come free.

In this article I will try to help you on how to send email from SQL server itself. It has worked well for me and for many as well. It’s easy, quick and sure solution that cover many scopes. I assume that you are aware of SQL server solution and have at least a little knowledge for queries.

Configuring Email Profile & Accounts

SQL Server solution window provides a wizard to help you make Email profile and account. Using these one can start sending Emails from SQL.

    •  Access Database Email from Management > Right click on Database Mail and click on Configure Database Mail.

Capture

    •  The system will take you to wizard. Click on “Set up Database Mail by performing the following tasks” and click Next.Capture
    •  Enter a profile name, Description and click Add button to create and add an account.Capture
    •  Fill in Account Name, Description, Email Address (from address), Display Name, Server Name, Port Number (Default 25), User Name (Should Exist on mail server) and password.
          •  Email Address (From) can be any Email address. It is not required that it should exist on your Email server. It is only for displaying when the Email is sent to anybody.
          •  Display Name will be displayed in the Email sent by system.
          •  Server Name & Port : Server name / IP should be same as your Email server. That is the server the SQL will require to send Emails. Port number generally remains 25, if it is not changed.Capture
        • You can have multiple profile and toggle between default profiles.Capture
        • Check the parametersCapture
        • Finish to complete.Capture

      OK, now you are done with creating profile & account. The database Email is ready to be used in SQL queries. Let me provide you one example on sending Email.
      ***************** IN SQL STORED PROCEDURE ****************
      EXEC msdb.dbo.sp_send_dbmail
      @profile_name=’ProfileName’,
      @recipients= @recipients,
      @copy_recipients = @copy_recipients,
      @blind_copy_recipients = @blind_copy_recipients,
      @subject=@subject,
      @body= @body,
      @body_format = @body_format,
      @importance = @importance,
      @sensitivity = @sensitivity,
      @file_attachments = @file_attachments,
      @from_address = @from_address,
      @reply_to = @reply_to

      *********************************************************
      The procedure “sp_send_dbmail” is pre-built procedure of SQL system which is there in msdb Data Base. When you will run the above query, the system will access the profile and account name and send Email based on the value provided in the parameters.

      The SQL Email can be very useful at times and is really fast and reliable. Added advantage is that in the table “[msdb].[dbo].[sysmail_mailitems]” you can find all the Emails sent / not sent. You do not have to maintain the trail anywhere else. You can also add attachments from a specific location and send Email. You can specify a query in the parameters and send the result as an attachment. I will cover more in my next article.

Tags:
  • Email from SQL Server,
  • SQL Server,
  • Leave a Reply