5 Feb 2009 apenwarr   » (Master)

Setting up a default/global mail account in database mail

Hi, Google. You kind of failed to help me out earlier when I was asking about "how to set a global mail profile for database mail in Microsoft SQL 2005." Here's what I wish you had said:

First of all, "Database mail" ("DBMail" or "Sysmail") is not the same as "SQL mail" ("SQLMail"). They're both stupid and overly complex, but DBMail is newer and slightly less stupid.

SQLMail uses an installed MAPI provider on your system to send mail, which means you need such a thing, possibly Outlook. DBMail apparently ignores your MAPI provider entirely. So if you find an article that says you need to install Outlook first, just ignore it; it's not true.

First, enable dbmail:

sp_configure 'Database Mail XPs', 1

RECONFIGURE

Then, create a dbmail account and profile:

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'TestAcct',
    @description = 'Mail account for use by all database users.',
    @email_address = 'test@example.com',
    @display_name = 'Test Server',
    @mailserver_name = 'smtp.example.com'

EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'TestProf', @description = 'Profile used for administrative mail.'

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'TestProf', @account_name = 'TestAcct', @sequence_number = 1

Next, you can set that dbmail profile as the "default profile" ("global profile") for all users (ie. the "public" group):

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'TestProf',
    @is_default = 1

And finally, try sending a test message:

EXECUTE msdb.dbo.sp_send_dbmail
    @recipients='test@example.com',
    @subject='test',
    @body='test'

And may I never have to look this up again.

Syndicated 2009-02-04 20:15:30 from apenwarr - Business is Programming

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!