Send mail using Sql server express 2005

• nidhivarshney82

  SSC Journeyman

  Points: 91

  More actions

  June 14, 2010 at 3:29 am

  #233732

  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

• Arjun SreeVastsva

  SSCertifiable

  Points: 7150

  More actions

  July 14, 2010 at 1:29 am

  #1192605

  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 ;

• Nice Marmot

  SSCrazy

  Points: 2278

  More actions

  July 15, 2010 at 6:05 am

  #1193408

  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