Sending EMail using SQL Server 2005Did anyone try sending email using SQL Server?
Ok I tried and got it working, so if you would like to know more please read on. If you face problems while trying this please contact me and I will try helping you sort out the problem.
SQL Server (SQLS) provides Stored Procedure (SP) s which we can use to send mails using SQL. There are two methods in SQL Server 2005.
1.) SQL Mail - SQL Server 2000 provides SQL Mail (The SP you have to use in this method is xp_sendmail.). This method is also supported in SQL Server 2005 (SQLS 2005). But this will not be supported in the next versions of SQL Server. If you are doing a new development the best is to use the Database Mail. Do know that SQL Mail is not supported on 64 Bit versions of SQLS and also remember that SQL Mail is not installed in SQLS 2005 by default.
2.) Database Mail - SQL Server 2005 provides a new method called Database Mail (The SP you have to use in this method is sp_send_dbmail.). This is new to SQL Server 2005 so I will be discussing how to send mails using Database Mail in the following sections of this article using a SP which I wrote as an example. The following SP will read a table and will generate a HTML report according to the data and will send it as an EMail.
Please note that in this article HTML, HEAD, BODY starting and ending tags are proceeded with _ since they are not allowed to be used inside of posts.
--****************************************************************************************
-- Original Developer - Arjuna Chiththananda
-- Description - Sending EMails in SQL Server.
-------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE [OWNER].[sp_SendEMail]
(
@ReportDate DATETIME
)
AS
BEGIN
SET NOCOUNT ON;
-- Declaring main variables required.
-- HTMLReport is used to store the report (web page) generated using HTML.
DECLARE @varReferenceNumber VARCHAR(10), @varPassengerName VARCHAR(100), @varDepartment VARCHAR(30), @varTravelInformation VARCHAR(100), @HTMLReport VARCHAR(MAX), @varSubject VARCHAR(75)
-- Starting the creation of web page report.
SET @HTMLReport = '<_html><_head><_title>Ar'
+ '<_body>
SQL Server EMail System
Email Report
'
+ '
Report Date -: ' + CONVERT(VARCHAR(15), @ReportDate, 105) + '
'
+ '
'
-- Checking a view for data.
IF (SELECT COUNT (*) FROM [OWNER].[vw_ListPassengers] WHERE ReserveDate BETWEEN @ReportDate AND @ReportDate) = 0
BEGIN
SET @HTMLReport = @HTMLReport + 'There are no records to display.'
END
ELSE
BEGIN
-- Creating the table required to format the displayed data.
SET @HTMLReport = @HTMLReport + '
'
+ ' '
+ ' Reference Number | '
+ ' Passenger Name | '
+ ' Department | '
+ ' Travel Information |
'
-- Declaring a cursor to loop through data.
DECLARE curPsngrList CURSOR
FOR SELECT [RefId], [PassengerName], [Department], [Address] FROM [OWNER].[vw_ListPassengers] WHERE ReserveDate BETWEEN @ReportDate AND @ReportDate
OPEN curPsngrList
FETCH NEXT FROM curPsngrList INTO @varReferenceNumber, @varPassengerName, @varDepartment, @varTravelInformation
WHILE @@FETCH_STATUS = 0
BEGIN
-- Adding rows to the table for the records found.
SET @HTMLReport = @HTMLReport + ' ' + @varReferenceNumber + ' | '
+ ' ' + @varPassengerName + ' | '
+ ' ' + @varDepartment + ' | '
+ ' ' + @varTravelInformation + ' |
'
FETCH NEXT FROM curPsngrList INTO @varReferenceNumber, @varPassengerName, @varDepartment, @varTravelInformation
END
-- Closing and deallocating the cursor.
CLOSE curPsngrList
DEALLOCATE curPsngrList
END
-- We are done with the report web page. Ending the web page properly.
SET @HTMLReport = @HTMLReport + '
'
-- Enabling Database Mail.
-- With the default settings Database Mail is not enabled to increase the performance and security of the server.
-- So before using Database Mail we have to enable it on the SQL Server. This is done by setting the configuration option 'Database Mail XPs'. But because is it an advanced configuration option first we have to enable showing advanced options by running the following.
EXEC master.dbo.sp_configure 'show advanced option', '1'
-- To apply the changes run RECONFIGURE.
RECONFIGURE
EXEC master.dbo.sp_configure 'Database Mail XPs', 1
-- On both above options 1 is used to enable the option and 0 is used to disable the option.
RECONFIGURE
-- Now we have enabled Database Mail now we have to start Database Mail.
EXEC msdb.dbo.sysmail_start_sp
-- To send EMails first you have to setup Database Mail. First you have to create an account.
-- I am getting the mail account settings from a table, following are the required variables.
DECLARE @varUserName NVARCHAR(128), @varPassword NVARCHAR(128), @varEMailAddress NVARCHAR(128), @varDisplayName NVARCHAR(128), @varMailServerName NVARCHAR(128), @varPort INT
-- Filling the required variables with the table data.
SELECT TOP 1 @varUserName=EMServerUserName, @varPassword=EMServerPassword, @varEMailAddress=EMSenderAddress, @varDisplayName=EMSenderName, @varMailServerName=EMServerName, @varPort=EMServerPort FROM [OWNER].[tblEMailServer] ORDER BY EMServerOrder, EMServerName
-- Checking whether the account already exists.
-- We can check for existing accounts in the sysmail_account table in the msdb database. As the final section of this article I will list down number of tables which will come in handy while you use Database Mail. (Note my account name is accArjuna.)
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysmail_account] WHERE [name] = 'accArjuna') = 0
BEGIN
BEGIN TRY
-- If the account does not exist we have to create the account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'accArjuna', -- Account name.
@username = @varUserName, -- Account user name to log in to the SMTP server ('Arjuna'),
@password = @varPassword, -- Password of the above mentioned account ('123').
@description = 'Arjuna Chith on Database Mail.', -- Description of the account.
@email_address = @varEMailAddress, -- EMail address to use to send the mails ('Arjuna@Company.Au').
@display_name = @varDisplayName, -- Name to display in the messages ('Arjuna Chiththananda').
@mailserver_name = @varMailServerName, -- SMTP mail server name ('Server.My.Company').
@port = @varPort -- Port of the mail server if it is diferent from 25 (25 is the default value).
END TRY
BEGIN CATCH
END CATCH
END
-- If the account already exists we will update it, since sometimes the details might have changed.
ELSE
BEGIN
EXECUTE msdb.dbo.sysmail_update_account_sp
@account_name = 'accArjuna', -- Account name.
@username = @varUserName, -- Account name to log in to the SMTP server ('Arjuna'),
@password = @varPassword, -- Password of the above mentioned account ('123').
@description = Arjuna Chith on Database Mail.', -- Description of the account.
@email_address = @varEMailAddress, -- EMail address to use to send the mails ('Arjuna@Company.Au').
@display_name = @varDisplayName, -- Name to display in the messages ('Arjuna Chiththananda').
@mailserver_name = @varMailServerName, -- SMTP mail server name ('Server.My.Company').
@port = @varPort, -- Port of the mai,server if it is diferent to 25 (25 is the default value).
@replyto_address = NULL, -- We can put a reply to address different from above address if we needs to make all replies to the send mail directed to another mail box.
@mailserver_type = 'SMTP', -- Mail server type. Currently only SMTP is supported.
@use_default_credentials = 0, -- Credentials to be used. (1 = SQL Server credentials, 0 = users the username and password given in the above and null = anonymous authentication.)
@enable_ssl = 0 -- If your mail server needs Secure Sockets Layer (SSL) then set it to 1 or make it 0. By making this 1 Database Mail will encrypt communication using SSL.
-- Instead of updating if you want we can delete and add the account again. That part is commented here because you don't need to have both update and delete + add. But if you want you can use this method also.
-- EXECUTE msdb.dbo.sysmail_delete_account_sp
-- @account_name = 'accArjuna' -- When deleting an account we need to give the account id or the name. I prefer giving the name.
-- Now we have deleted the account we will add the modifications as a new account.
-- EXECUTE msdb.dbo.sysmail_add_account_sp
-- @account_name = 'accArjuna', -- Account name.
-- @username = @varUserName, -- Account user name to log in to the SMTP server ('Arjuna'),
-- @password = @varPassword, -- Password of the above mentioned account ('123').
-- @description = 'Arjuna Chith on Database Mail.', -- Description of the account.
-- @email_address = @varEMailAddress, -- EMail address to use to send the mails ('Arjuna@Company.Au).
-- @display_name = @varDisplayName, -- Name to display in the messages ('Arjuna Chiththananda').
-- @mailserver_name = @varMailServerName, -- SMTP mail server name ('Server.My.Company').
-- @port = @varPort -- Port of the mail server if it is diferent from 25 (25 is the default value).
END
-- Now we have to add a Profile.
-- First check whether the profile exists.
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysmail_profile] WHERE [name] = 'prfArjuna') = 0
BEGIN
BEGIN TRY
-- If not add the profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'prfArjuna', -- Profile name to be used.
@description = 'Arjunas Profile' -- Profile description to be used.
END TRY
BEGIN CATCH
END CATCH
END
-- Now we have added an account and a profile we need to merge both these that is done here after checking whether it exists.
IF (SELECT COUNT(*) FROM [msdb].[dbo].[sysmail_profileaccount] WHERE [profile_id] IN (SELECT [profile_id] FROM [msdb].[dbo].[sysmail_profile] WHERE [name] = 'prfArjuna') AND [account_id] IN (SELECT [account_id] FROM [msdb].[dbo].[sysmail_account] WHERE [name] = 'accArjuna')) = 0
BEGIN
BEGIN TRY
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'prfArjuna', -- Profile to be linked.
@account_name = 'accArjuna', -- Account to be linked.
@sequence_number = 1 -- Determines the order of account usage if you have more than one account. If the first account fails it will try sending mails with the second one in the order.
END TRY
BEGIN CATCH
END CATCH
END
-- Grants the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
BEGIN TRY
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'prfArjuna', -- Profile name to set.
@principal_name = 'public', -- Makes this profile a public profile.
@is_default = 1 -- Makes the profile the default profile for the principal.
END TRY
BEGIN CATCH
END CATCH
-- Fire RECONFIGURE to make the changes apply to SQL Server.
RECONFIGURE
-- The variable to hold the recipient list of our mail.
DECLARE @varRecipients VARCHAR(MAX)
-- Filling recipient list from the table (Note here I am getting the first one in the table (Which will be the last entry added).)
SELECT TOP 1 @varRecipients=EMailAddress FROM [OWNER].[tblEMail] WHERE EMailActive=1 ORDER BY EMailId DESC
-- Setting the subject of the EMail.
SET @varSubject = 'EMailing in SQL Server on ' + CONVERT(VARCHAR(15), @ReportDate, 105) + '.'
-- We have done the hard part so we will now send our mail.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'prfArjuna', -- Profile name to be used when sending mail.
@recipients = @varRecipients, -- Recipients who will get our mail. ('Arjuna@Company.Au).
@subject = @varSubject, -- Adding the subject to the mail.
@body_format = 'HTML', -- Format of the mail, this could be Text or HTML.
@body = @HTMLReport -- Message body of the mail.
-- If you want to mail an output of a query you can give your query as follows.
--@query='SELECT RefId FROM FlightBooking.SE.Vw_Report_ListPassengers'
END
--****************************************************************************************
Now we have finished the SP remember to run this sp under a sysadmin or serveradmin role to make sure the reconfigurations will take effect.
When we do Database Mail related things like creating accounts, profiles etc everything will be stored in the msdb. If you would like to query out the statuses, following tables might be helpful.
-- Lists down all the created mail accounts.
SELECT * FROM [msdb].[dbo].[sysmail_account]
-- Lists down all the attachments sent using Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_attachments]
-- Lists down all the created attachments using Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_attachments_transfer]
-- Lists down the configuration settings of Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_configuration]
-- Lists down all the created mail servers.
SELECT * FROM [msdb].[dbo].[sysmail_server]
-- Lists down the activity log of Database Mail, if any error occurs a log entry will be created.
SELECT * FROM [msdb].[dbo].[sysmail_log]
-- Lists down the mails sent from Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_mailitems]
-- Lists down the principal accounts configured.
SELECT * FROM [msdb].[dbo].[sysmail_principalprofile]
-- Lists down all the created profiles.
SELECT * FROM [msdb].[dbo].[sysmail_profile]
-- Lists down all the created profile accounts.
SELECT * FROM [msdb].[dbo].[sysmail_profileaccount]
-- Lists down the details of results that were returned from the queries that were run using Database Mail.
SELECT * FROM [msdb].[dbo].[sysmail_query_transfer]
-- Lists down the details of retry attempts made.
SELECT * FROM [msdb].[dbo].[sysmail_send_retries]
-- Lists down all the created mail servers.
SELECT * FROM [msdb].[dbo].[sysmail_server]
-- Lists down the mail server type (SMTP) and some settings.
SELECT * FROM [msdb].[dbo].[sysmail_servertype]
