Sql Server Send Email...

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

 

 

 

/* Author: lilo.zhu at 2009-07-23

   Description: Scan IBL system missing Reject

   Log: ----

*/

 

ALTER procedure [dbo].[proc_missing_reject]

as

declare @int_count int

declare @sublot_no varchar(30),

       @lot_no varchar(30),

       @prod_order_no varchar(30),

       @status varchar(10),

       @creation_date varchar(20)

 

 

Declare @profile_name varchar(20),

       @rec_address  varchar(50)

 

DECLARE @Message nvarchar(max)

DECLARE @Subject nvarchar(100)

 

Set @rec_address=N'lilo.zhu@gmail.com'

Set @profile_name=N'DEVDB_MAIL'

 

declare  @tb_mr table

(

sublot_no varchar(50),

lot_no varchar(50),

prod_order_no varchar(50),

status varchar(50),

creation_date varchar(50)

)

insert into @tb_mr

select sublot_no,Lot_no,prod_order_no,status,creation_date

 from srv_asat.asat.dbo.tb_sublotext

where status='UNP'

and CB_ENDSHIP='1'

and creation_date>'2009-07-23'

and sublot_no

in

(select sublot_no from srv_asat.asat.dbo.tb_sublotext

where status='ALLPACK'

and creation_date>'2009-07-23')

 

select @int_count=count(*) from @tb_mr

 

 

if @int_count>0

begin

insert into TB_Missing_Reject

    select sublot_no,Lot_no,prod_order_no,status,creation_date,Convert(varchar(20),getdate(),120)

    from @tb_mr

 

Select @subject ='[Date: ]'+Convert(varchar(20),getdate(),120)+'scaning have missing reject...'

     

SET @Message= N'<H1>IBL System Missing Reject Report</H1>' +

      N'<table border="1">' +

      N'<tr><th>Sublot_No</th><th>Lot_No</th>' +

      N'<th>Prod_Order_No</th><th>Status</th><th>Creation_Date</th></tr>' +

      CAST ( (  Select td =Sublot_no,'',

                     td =Lot_No,'',

                     td =Prod_order_No,'',

                     td =Status,'',

                     td =Creation_Date

              from @tb_mr

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

 

      EXEC msdb.dbo.sp_send_dbmail

      @profile_name = @profile_name,

      @recipients = @rec_address,

      @subject = @Subject,    

      @body = @Message,

      @body_format = 'HTML' ;   

END