sp_send_dbmail for SQL Server Express '05

Question

sp_send_dbmail for SQL Server Express '05

Shairal-763732

SSC-Addicted

Points: 436
More actions
August 20, 2009 at 1:32 pm

#209719

I’m am seeing conflicting data on whether or not I can use sp_send_dbmail on Express Edition of SQL Server ’05 (we are on sp 4). Will someone please tell me if this can be done, and if so how.
Thanks in advance for your assistance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

SQL 2005 what sp? I am assuming SP4 for the OS, which would likely be 2000 if you are talking about SP4 and 2000 is the only one I know of with an SP4.

You have to setup a mail profile. Which is pretty easy from the GUI. If you don't have the GUI that will do that then download and install SQL 2008 Express with advanced features. The Management Studio with that should probably work.

CEWII

dilip_baboo SSC Enthusiast Points: 195 More actions · Answer 2

Step 1

Open SQL server --> Management --> Database Mail --> use the wizard to set up your SMTP server and database email profile [your DB email profile]

Step 2

Make sure you have the dbo access

EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'your DB email profile',

@recipients = 'your_email@email.com',

@body = 'Test Email',

@subject = 'Test Email',

@exclude_query_output = 1 --if you want not to see the output msg

Shairal-763732 SSC-Addicted Points: 436 More actions · Answer 3

Thanks for replies!

My OS is Windows NT 5.0 sp4.

I have configured the server:

use master

go

sp_configure 'show advanced options',1

go

reconfigure with override

go

sp_configure 'Database Mail XPs',1

go

reconfigure

go

And created a profile and set it as default:

-- Create a New Mail Profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DefaultMail',

@description = 'Mail profile for Database Mail'

GO

-- Set the New Profile as the Default

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'DefaultMail',

@principal_name = 'public',

@is_default = 1 ;

GO

And created an account:

-- Create an Account

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'SQLExpressMail',

@description = 'SQL Express Mail Account',

@email_address = 'me@work.com',

@display_name = 'Me at Work',

@mailserver_name = 'xx.xx.x.xx' -- used IP address for mail server

I then tested the mail:

EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'DefaultMail',

@recipients = 'me@work.com',

@Subject = 'Test Message',

@Body = 'This is a test message from SQL Server DatabaseMail'

It says "Mail queued" but it never comes through. I have confirmed that the Broker is enabled and sysmail is started. I'm sure there's something very simple I'm missing - but being a product of the "wizard" era, I just can seem to find it. Unfortunately moving to Express '08 is not an option at this time. Any other suggestions?

Shairal-763732 SSC-Addicted Points: 436 More actions · Answer 4

Found the problem - there were two versions of sp 2 for SQL Express '05 - upgraded to the latest and it seems to be working okay now.

Thanks for the responses!