January 6, 2009 at 8:58 am
Hello,
I am thinking about upgrading from a SQL 2000 database to SQL 2008. Currently I have an SQL 2000 database that handles all emails for my applications. It is a pretty simple database that keeps tracks of outgoing emails for all my applications. I have a stored procedure which is used by other applications to create emails and a job that uses a COM component to send out the emails. I am unfamiliar with the workings of 2008 but I was wondering if it offers a similar solution for me? As I would like to get away from the legacy technology (COM component for sending emails).
Thank you!
January 6, 2009 at 9:42 am
I put together this script a few weeks back when I had to set up a mass number of SQL 2008 instances, and use a standard convention for any emails (for example coming from the servername or servername/instance).
This script enables the Database Mail XP's, which you'll need. Then there are a bunch of procedure calls that will set up the profile and finally it will send a test email.
You can obviously change any component of this, for example the account names, description, profile etc, and you'll have to add your smtp information, but it's a good place to start.
/****** Collect the server and instance information, we will use this to autogen the mail stuff ******/
declare @server nvarchar(100) = CONVERT(nvarchar(100), SERVERPROPERTY('machinename'))
, @INSTANCE nvarchar(100) = CONVERT(nvarchar(100), SERVERPROPERTY('instancename'))
, @EMAILADDY nvarchar(100)
if @INSTANCE IS NOT NULL
begin
SELECT @INSTANCE = '.'+@INSTANCE
SELECT @EMAILADDY = @server+@INSTANCE+'@yourcompany'
end
if @INSTANCE IS NULL
BEGIN
SELECT @EMAILADDY = @server+'@yourcompany'
END
/******
enable the ability to send database mail we have to change to show advanced options on
othersise we cant enable the XPs we turn it off again aferwards
******/
exec sp_configure 'show advanced options', 1
reconfigure
/****** enable the mail xps ******/
exec sp_configure 'Database Mail XPs', 1
reconfigure
exec sp_configure 'show advanced options', 0
reconfigure
IF EXISTS (select name from msdb.dbo.sysmail_account where name = @@SERVERNAME)
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = @@SERVERNAME
/****** now add the account for sending email this is the one with the configuration ******/
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @@SERVERNAME,
@description = 'Mail account for administrative e-mail.',
@email_address = @EMAILADDY,
@display_name = @@SERVERNAME,
@mailserver_name = 'your smtp server' ;
/****** now we add the profile ******/
IF EXISTS(select name from msdb.dbo.sysmail_profile where name = N'DBA_Mail')
EXEC msdb.dbo.sysmail_delete_profile_sp @profile_name = N'DBA_Mail'
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'DBA_Mail',
@description=N'Default Profile'
/****** associate the profile to the account ******/
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'DBA_Mail',
@account_name= @@SERVERNAME,
@sequence_number=1
/****** make the profile public (anyone can send through it, maybe lock down) ******/
EXEC msdb.dbo.sysmail_delete_principalprofile_sp @principal_name=N'guest',
@profile_name=N'DBA_Mail'
/****** sets the DBA_Mail profile as the default ******/
EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest',
@profile_name=N'DBA_Mail', @is_default=1
/****** Send a test email to ensure the configuration is good ******/
exec msdb.dbo.sp_send_dbmail @profile_name = null --uses the default profile
, @recipients = 'you@yourcompany'
, @subject = 'Test Email'
, @body = 'This is just a test email and can be ignored'
, @body_format = 'HTML' --default is TEXT
January 6, 2009 at 9:47 am
This is great but I think the only thing I am missing is the ability to specify a custom from name and e-mail addy. I checked out the params on sp_send_dbmail and it didn't seem to support it. I guess the other way would be to create a profile for each custom from email.
But then again I am a newbie so I might be missing something 🙂
Any other suggestions?
January 6, 2009 at 9:55 am
I'm only familiar with creating a profile for each "from" address. It's pretty quick to script and the profiles are the best thing if you are sending multiple emails.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply