Tuesday, May 23, 2006

SQL 2000 : Sending Mail WITHOUT Sql Mail

Creating the procedure -

create procedure [dbo].[CdoSysEMail] 
 (
 @email_from    varchar(200), 
 @email_to      varchar(200), 
 @email_bcc     varchar(200) = '', 
 @email_subject varchar(200) = '', 
 @email_body    varchar(7000) =''
 )
as 

begin
 -- declare variables
 declare @obj_message_id int 
 declare @output int 
 declare @error_output varchar(5000) 
 declare @error_source varchar(255) 
 declare @error_description varchar(1000) 
  
 -- create CDO.Message object 
 exec @output = sp_OACreate 'CDO.Message', @obj_message_id OUT 

 -- set server properties in the object
 exec @output = sp_OASetProperty @obj_message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' 
 exec @output = sp_OASetProperty @obj_message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25'
 exec @output = sp_OASetProperty @obj_message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','0'
 exec @output = sp_OASetProperty @obj_message_id, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '193.19.88.36'

 -- save those properties
 exec @output = sp_OAMethod @obj_message_id, 'Configuration.Fields.Update', Null
 
 -- pass the procedure parameters through to the e-mail
 exec @output = sp_OASetProperty @obj_message_id, 'To', @email_to
 exec @output = sp_OASetProperty @obj_message_id, 'Bcc', @email_bcc
 exec @output = sp_OASetProperty @obj_message_id, 'From', @email_from
 exec @output = sp_OASetProperty @obj_message_id, 'Subject', @email_subject
 exec @output = sp_OASetProperty @obj_message_id, 'TextBody', @email_body

 -- send the email
 exec @output = sp_OAMethod @obj_message_id, 'Send', NULL
 
 -- error handling 
 if @output <> 0 
 begin
  declare @No Int
  Set @No=@output
  Select @No
      exec @output = sp_OAGetErrorInfo NULL, @error_source OUT, @error_description OUT
  if @output = 0
   begin
   Set @error_output = 'Error : ' + cast(@No As varchar) + ', Source: ' + @error_source + ', Description: ' + @error_description
   Raiserror(@error_output,16,1)
   end
  else
   begin
      Raiserror('could not determine error (sp_OAGetErrorInfo failed)', 16,1)
      end
 end
 
 -- clean up
 exec @output = sp_OADestroy @obj_message_id
end
go


Testing the procedure -

execute CdoSysEMail 
 @email_from ='sqladmin@mydomain.net',
 @email_to = 'recipient@mydomain.net', 
 @email_subject = 'test, please ignore!', 
 @email_body = 'test message body'

No comments: