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