SQL Server 2005 Database Mail Setup Using T-SQL

Starting with SQL Server 2005, Database Mail is no longer required to use MAPI client in order to run. You can now setup Database Mail to use Simple Mail Transfer Protocol (SMTP) to send mail from your database engine. Below is a script I have used to setup on my SQL Server 2005, but you should also be able to use the same script on the newer version of SQL Server 2008.

   You may also use the built in Database Mail configuration wizard in Management Studio, under your instance’s Management folder, by right clicking on Database Mail, and selecting Configure Database Mail. (I may include another article later with screenshots on how to setup database mail using the wizard).

   Before you run the script, you need to make sure that Database Mail is enabled for your database instance. You can enable that by going into your SQL Server surface area configuration application, under database engine -> database mail, and check the Enable Database Mail stored procedures option.

   The first step is to setup a new profile:

-- Create new profile EXECUTE msdb.dbo.sysmail_add_profile_sp   @profile_name = 'Profile Name',   @description = 'Profile Description' GO -- Set the New Profile as the DefaultEXECUTEmsdb.dbo.sysmail_add_principalprofile_sp   @profile_name = 'Profile Name',   @principal_name = 'public',   @is_default = 1 ; -- Make this the default profile GO

Once the profile is created and set as default, create a new account:

-- Create an New E-mail AccountEXECUTEmsdb.dbo.sysmail_add_account_sp   @account_name = 'accountname',   @description = 'Mail account descriptions',   @email_address = 'youraddress@yourdomain.com',   @display_name = 'account display name',   @replyto_address = 'replytoaddress@yourdomain.com',   @mailserver_name = 'smtp.yourdomain.com',   @username = 'username',   @password = 'password',   @port = 465, -- Check with your admin for correct port  @enable_ssl = true -- Enable ssl communication GO

NOTE: Check with your mail provider for you e-mail server settings. Also, make sure that the port you are using is open and not blocked by a firewall or other security application. You may receive Time Out errors if the port is not open in your mail log file.

Now add the account to the profile:

-- Add the Account to the ProfileEXECUTEmsdb.dbo.sysmail_add_profileaccount_sp   @profile_name = 'Profile Name',   @account_name = 'accountname',   @sequence_number = 1 GO

   You can now test sending an e-mail from your newly setup database mail by using the sp_send_dbmail stored procedure using something like this:

EXECUTE msdb.dbo.sp_send_dbmail @recipients='receipient@recipientdomain.com', @subject = 'Test e-mail sent from database mail', @body = 'This is a test message sent from the newly created database mail account', @reply_to = 'youremail@yourdomain.com' GO

   Please let me know what you think.

  PS: All scripts and code is offered ‘as is’, and you are responsible for any use of it. You should do your own testing before using it.

Posted in: SQL Server 2005 Database Mail Setup Using T-SQL