sqlserver 2000ストレージ・プロシージャによるメール

alter proc exec_sendmail1
@appname varchar(100), --   
@guid varchar(100),    --  guid
@id varchar(100),      --    
@title varchar(500)    --    
as

declare @from varchar(500)  --   
declare @smtpserver varchar(200) --smtp   
declare @sendusername varchar(200) --    :   
declare @sendpassword varchar(200) --    :   
declare @content varchar(2000) --    

select @from = '[email protected]'
select @smtpserver = '10.25.21.51'
select @sendusername = '[email protected]'
select @sendpassword = '20369845'
select @content = @appname+'   '+@title+'('+@id+')'+'，     ，   ，   ！'

--      
if @appname = '' or  @guid = '' or @id = '' or @title = ''
begin  
  raiserror 50000 'please set the @appname and  @guid and @id and @title values before excute the stored procedure'  
  return -1  
end

--        
declare @count int
set @count = ((select count(*) from sys_inst_prcs 
              where taskid = @guid 
              and prc_id >= 
              (
                select top 1 prc_id from sys_inst_prcs 
                where selactname = 0 
                and taskid = @guid 
                and procuser is not null 
                order by prc_id desc
              ))-1)
print @count
--     
create table #temp(name varchar(50))

--           
set rowcount @count  --       count
declare tempCursor cursor 
for
  select procuser from sys_inst_prcs 
  where taskid = @guid 
    and prc_id >= 
    (
      select top 1 prc_id from sys_inst_prcs 
      where selactname = 0 
      and taskid = @guid  
      and procuser is not null 
      order by prc_id desc
    ) 

open tempCursor
declare @name varchar(50) --   
fetch next from tempCursor into @name
while @@fetch_status=0
begin

--        
insert into #temp(name) values(@name)
fetch next from tempCursor into @name
end
close tempCursor
deallocate tempCursor

--       
declare sendMailCursor cursor
for
  select distinct(name) from #temp 
  
open sendMailCursor
declare @receiptant varchar(50) --   
declare @to varchar(500) --   

fetch next from sendMailCursor into @receiptant
while @@fetch_status=0
begin
--      send_mail   
set @to = (select email from sys_user where username = @receiptant)
exec send_mail @from=@from, @to = @to,@smtpserver = @smtpserver,@sendusername = @sendusername,@sendpassword = @sendpassword,@subject = @title,@body = @content
fetch next from sendMailCursor into @receiptant
end
close sendMailCursor
deallocate sendMailCursor

go


exec exec_sendmail1 '  ','dad42bed-73c3-48aa-ab06-7962b335f0ff','NJSQ11060805383','      '

ALTER PROCEDURE [dbo].[send_mail]  
   @From varchar(1000) ='',  --     
   @To varchar(1000) ,   --     
   @smtpserver varchar(200),--smtp   
   @sendusername varchar(200),--    :   
   @sendpassword varchar(200),--    :   
   @Subject nvarchar(128)='', --    
   @Body nvarchar(4000) ='' --    
   with encryption  
      
   /*********************************************************************  
      
   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)     
    
  if @sendusername='' or  @sendpassword=''  
  begin  
   raiserror 50000 'please set the @sendusername and  @sendpassword values before excute the stored procedure'  
   return -1  
  end     
  --replace the quotation marks  
  set @Subject=replace(@Subject,'''','''''')  
  set @Body=replace(@Body,'''','''''')     
  --************* 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', @smtpserver  
  --      ：        0，   ，        
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','0'     
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @sendusername  
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @sendpassword  
     
  -- 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  
  --   HTMLBody  ，     HTMLBodyPart，       
  EXEC @hr = sp_OASetProperty @IMsg, 'HTMLBodyPart.Charset', 'gb2312'
  EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL  
     
  if @@error<>0 or @hr<>0  
  begin  
   raiserror 55000 '<send_mail> Error: send mail failed.'  
  end  
  else   
  begin  
   print 'Success: send mail ok.'  
  end  
     
  EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT  
  IF @hr = 0  
  BEGIN  
   SELECT @output = '<send_mail> Error Source: ' + @source  
   PRINT  @output  
   SELECT @output = '<send_mail> Error Description: ' + @description  
   PRINT  @output  
  END  
  ELSE  
  BEGIN  
   PRINT '  sp_OAGetErrorInfo failed.'  
   RETURN  
  END  
    
  -- Do some error handling after each step if you have to.  
  -- Clean up the objects created.  
  EXEC @hr = sp_OADestroy @iMsg  
   
  GO