performance problem sending email using cdonts.dll in MS SQL 2000

• Helen Dyakonova

  SSC-Addicted

  Points: 465

  More actions

  April 29, 2011 at 1:18 pm

  #145263

  Hi everybody,

  I have performance problem sending email using cdonts.dll in MS SQL 2000. I have a lot of emails for sending and create this code for sending each email:

  EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT

  IF @result <> 0

  BEGIN

  --PRINT 'sp_OACreate Failed'

  SELECT@ret = -3000,

  @err_mess = 'Error when create cdonts'

  GOTO CurCloseError

  END

  EXEC @result = sp_OASetProperty @object, 'From',@fromeEmail

  IF @result <> 0

  BEGIN

  --PRINT 'sp_OASetProperty Failed for From'

  SELECT@ret = -3001,

  @err_mess = 'Error when set property'

  GOTO CurCloseError

  END

  EXEC @result = sp_OASetProperty @object, 'Body', @body_

  IF @result <> 0

  BEGIN

  --PRINT 'sp_OASetProperty Failed for Body'

  SELECT@ret = -3002,

  @err_mess = 'Error when set property'

  GOTO CurCloseError

  END

  -- EXEC @result = sp_OASetProperty @@object, 'BCC',@BCC

  EXEC @result = sp_OASetProperty @object, 'Subject', @subject

  IF @result <> 0

  BEGIN

  --PRINT 'sp_OASetProperty Failed for Subject'

  SELECT@ret = -3003,

  @err_mess = 'Error when set property'

  GOTO CurCloseError

  END

  EXEC @result = sp_OASetProperty @object, 'To', @userEmail

  IF @result <> 0

  BEGIN

  --PRINT 'sp_OASetProperty Failed for To'

  SELECT@ret = -3004,

  @err_mess = 'Error when set property'

  GOTO CurCloseError

  END

  IF @CC <> ''

  BEGIN

  EXEC @result = sp_OASetProperty @object, 'CC', @CC

  IF @result <> 0

  BEGIN

  PRINT 'sp_OASetProperty Failed for CC'

  SELECT@ret = -3005,

  @err_mess = 'Error when set property'

  GOTO CurCloseError

  END

  END

  IF LEN(@bodyAll) > 7200

  BEGIN

  EXEC @result = sp_OAMethod @object, 'AttachFile', NULL, @FileName

  IF @result <> 0

  BEGIN

  --PRINT 'sp_OAMethod Failed: could not add attachment'

  SELECT@ret = -3010,

  @err_mess = 'Error when adding attachment'

  GOTO CurCloseError

  END

  END

  EXEC @result = sp_OAMethod @object, 'Send', NULL

  IF @result <> 0

  BEGIN

  --PRINT 'sp_OAMethod Failed for Sending'

  SELECT@ret = -3006,

  @err_mess = 'Error when sending'

  GOTO CurCloseError

  END

  --Destroy the object, if @result <> 0, there is an error.

  ----PRINT 'Destroying the CDONTS.NewMail object'

  EXEC @result = sp_OADestroy @object

  IF @result <> 0

  BEGIN

  --PRINT 'sp_OADestroy Failed'

  SELECT@ret = -3007,

  @err_mess = 'Error when destroy object'

  GOTO CurCloseError

  END

  But it works very slowly. Anyway to improve it? I need to include copy recipients and attachment as stated in code.

  Thanks in advance.