use of stored procedure to send email notification

• @DJ

  SSCommitted

  Points: 1989

  More actions

  July 8, 2005 at 7:29 am

  #93507

  Hi

  I am trying to get my ms sql to send me an email on certain occations but I can not get it to send.

  Have created the following:

  CREATE Procedure sn_SMTPMail

  AS  

  SET nocount on  

  declare @oMail int  

  DECLARE @counters int

  DECLARE @users int

  DECLARE @rest int

  SELECT @counters=topp from [counts] where id = 1

  SELECT @users=count(*) from where user_name = 'test'

  set @rest = (select topp from [counts] where id = 1) - (select count(*) from where user_name = 'test')

  declare @result int  

  EXEC @result = sp_OACreate 'CDONTS.NewMail', @oMail OUT  

  if @counters-@users < 1

    

   DECLARE @t varchar(50)

   SELECT @t=responsible from [counts] where id = 1

   EXEC @result = sp_OASetProperty @oMail, 'From', 'system@abcde.com'

   EXEC @result = sp_OASetProperty @oMail, 'To', 'admin@abcde.com'

   EXEC @result = sp_OASetProperty @oMail, 'Subject', @t

   EXEC @result = sp_OASetProperty @oMail, 'Body', 'No more ID'

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

   EXEC sp_OADestroy @oMail

   print cast(@rest as varchar) + 'trys'

  SET nocount off

  GO

  What am I doing worng? Do I have to set something up on the server for this to work? Should I solve diffrently?

  Thanks for all help

   

  Regards

  Dan

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  July 8, 2005 at 7:48 am

  #572327

  I'm not the expert, but I know that they'll want to know this :

  What error message are you getting?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736567

  More actions

  July 8, 2005 at 8:07 am

  #572357

  Have you checked that the SMTP server is receiving the message? It looks like it should work, but honestly, I've had issues with CDO/CDONTS. I used ASPEmail from my SQL Server and that worked well and most people swear by xp_smtp at sqldev.net. I'm wary of SP-OA since you could get memory leaks in there.

• @DJ

  SSCommitted

  Points: 1989

  More actions

  July 23, 2005 at 12:54 am

  #576606

  Hi.

  Sorry for extreme late respons but I forgott all about this because of some other hot issues and then my summer holiday set in.

  How do I check that SMTP server get the message?

  You talk about ASPEmail, does this require something special or is this a built in function in MS SQL?

  Thank you for all the help you give

   

  Regards

  Dan