sql server 2005 how do i turn on sp_send_dbmail

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

sql server 2005 how do i turn on sp_send_dbmail

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-02-18 : 14:28:14

Hi everyone.
I've just installed sql server 2005 (evaluation) and i need to turn the option of using sp_send_dbmail on. I tried books on line but didnt manage to understand how it is done.
Thanks

CShaw
Yak Posting Veteran

65 Posts

Posted - 2008-02-18 : 20:02:56

Have you tried this?

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Chris Shaw
www.SQLonCall.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-18 : 20:39:33

Check how to setup db mail in books online.

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-02-19 : 18:57:31

May be these STEPS might be helpful to configure the Database Mail.

After CSHAW code..........

Database Mail stores the log information on MSDB database and it can be queried using sysmail_event_log.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'company_Account',
@description = 'Mail account for Database Mail',
@email_address = ‘email address of the person in the organisation’,
@display_name = 'xxxx',
@username= email address of the person in the organisation’,
@password='ppp',
@mailserver_name = ‘smtp mail server name here’

Create"company_Account" using the sysmail_add_profile procedure to create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘company_Account ‘,
@description = 'Profile used for database mail'

Now execute the sysmail_add_profileaccount procedure

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'company_profile',
@account_name = 'company_Account',
@sequence_number = 1

Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'company_Profile',
@principal_name = 'public',
@is_default = 1;

Now let us send a test email from SQL Server

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients=’Recipient name email address’,
@subject = 'My Mail Test',
@body = @body1,
@body_format = ‘HTML’;

You may get the error message below, if you haven't run the SQL statements from step 1.

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of
Component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Database Mail XPs' by using sp_configure. For more information about enabling 'Database Mail XPs', see "Surface Area Configuration" in SQL Server Books Online.

You may see this in the database mail log if port 25 is blocked.

You can check the configuration of the Database Mail profile and account using SQL Server Management Studio by right clicking Database Mail and clicking the Configuration.

The log related to Database Mail can be viewed by executing the statement below.

SELECT * FROM msdb.dbo.sysmail_event_log

Now need to configure with sp_send_dbmail for the rest of the team.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-02-21 : 03:27:21

Thanks, i'll have to learn and understand it and then use it.

vasanthhepsy
Starting Member

48 Posts

Posted - 2008-02-21 : 04:40:48

Each of them is the Steps as you need to execute them on SSMS and get the code done. Only thing is you need to give the Sender and receiver Email addresses in the respective places and later test it as they are direct execution steps.

It deals with testing of the DBMAIL as you use the SMTP protocol.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources