June 14, 2010 at 3:29 am
Hello
I am using following procedure to send mail using sql server express 2005.
Procedure is executing successfully.but messages are not queueing up.
I am trying it on the local system. I am not having SMTP server name.
If the mail is getting queued up, then i can assume that after giving SMTP configuration it will work fine.
Please provide some solution. Thanks in advance.
create Procedure [dbo].[sp_SMTPMail]
@SenderName varchar(100),
@SenderAddress varchar(100),
@RecipientName varchar(100),
@RecipientAddress varchar(100),
@Subject varchar(200),
@Body varchar(8000),
@MailServer varchar(100) = '127.0.0.1'
AS
SET nocount on
declare @oMail int --Object reference
declare @resultcode int
EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT
if @resultcode = 0
BEGIN
EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress
EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress
EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body
EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL
EXEC sp_OADestroy @oMail
END
SET nocount off
July 14, 2010 at 1:29 am
Use the sysmail_add_account procedure as follows to create a Database Mail account, using mail.dynanet.com as the mail server and dinesh@dynanet.com as the e-mail account:
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Dinesh',
@description = 'Dinesh Mail on dynanet.',
@email_address = 'dinesh@dynanet.com',
@display_name = 'Dinesh Asanka',
@mailserver_name = 'mail.dynanet.com'
Use the sysmail_add_profile procedure to create a Database Mail profile called Dinesh Mail Profile:
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Dinesh',
@description = 'Dinesh Profile'
User the sysmail_add_profileaccount procedure to add the Database Mail account and Database Mail profile you created in previous steps.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Dinesh',
@account_name = 'Dinesh',
@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 = 'Dinesh',
@principal_name = 'public',
@is_default = 1 ;
July 15, 2010 at 6:05 am
Database Mail and SQL Mail are not available in SQL Server 2005 Express Edition. (See the BOL topic 'Features Supported by the Editions of SQL Server 2005'.) HTH
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply