Problem sending mail using cdosysmail

• nikhil.verma

  SSCrazy

  Points: 2603

  More actions

  January 20, 2009 at 8:29 am

  #127468

  Hi

  I am using sp_send_cdosysmail to send email from my one the prod servers.

  Its been 2 days the email are not getting delivered. I am getting the following error message whenver my job runs this SP :

  Source: CDO.Message.1

  Description: The "SendUsing" configuration value is invalid.

  My enviroment consists of :

  SQL SERVER : 2000

  windows 2000 server

  SP code is :

  alter PROCEDURE [dbo].[sp_send_cdosysmail]

  @From varchar(100) ,

  @To varchar(100) ,

  @Subject varchar(100)=" ",

  @Body varchar(8000) =" "

  /*********************************************************************

  This stored procedure takes the parameters and sends an e-mail.

  All the mail configurations are hard-coded in the stored procedure.

  Comments are added to the stored procedure where necessary.

  References to the CDOSYS objects are at the following MSDN Web site:

  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

  ***********************************************************************/

  AS

  Declare @iMsg int

  Declare @hr int

  Declare @source varchar(255)

  Declare @description varchar(500)

  Declare @output varchar(1000)

  --************* Create the CDO.Message Object ************************

  EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

  --***************Configuring the Message Object ******************

  -- This is to configure a remote SMTP server.

  -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp

  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

  -- This is to configure the Server Name or IP address.

  -- Replace MailServerName by the name or IP of your SMTP Server.

  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mailhost.ert.abc.com'

  -- Save the configurations to the message object.

  EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

  -- Set the e-mail parameters.

  EXEC @hr = sp_OASetProperty @iMsg, 'To', @To

  EXEC @hr = sp_OASetProperty @iMsg, 'From', @From

  EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

  -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

  EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

  EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

  -- Sample error handling.

  IF @hr <>0

  select @hr

  BEGIN

  EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

  IF @hr = 0

  BEGIN

  SELECT @output = ' Source: ' + @source

  PRINT @output

  SELECT @output = ' Description: ' + @description

  PRINT @output

  END

  ELSE

  BEGIN

  PRINT ' sp_OAGetErrorInfo failed.'

  RETURN

  END

  END

  -- Do some error handling after each step if you have to.

  -- Clean up the objects created.

  EXEC @hr = sp_OADestroy @iMsg

  Please let me know how to proceed on this.

  Thanks