SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON


/*
EXEC sp_configure 'Database Mail XPs'
go*/
/*management database mailden MAIL_PROFILE isminde profil yarat*/

DECLARE @Delimiter Char(1)
DECLARE @Dosyaadi VARCHAR(249)

SET @Delimiter = CHAR(9) /*Tab char(9) Line feed char(10) Carriage return char(13)*/
SET @Dosyaadi='Sanayi_Güvenli_Stok_Seviye_Kontrol_'+CONVERT(VARCHAR(10),GETDATE(),104)+'-'+ REPLACE( CONVERT(VARCHAR(8), CONVERT(TIME, GETDATE())),':','.')+'.xls';

EXEC MSDB.dbo.sp_Send_DBMail
@profile_name = 'MAIL_PROFILE',
@Recipients='ALICI@MAIL.com',
@Subject='SUBJECT',
@Body='BODY',
@Query='select TOP 10 * from LG_101_ITEMS;',
@Attach_Query_Result_As_File = 1,
@Query_Result_Header = 1,
@Query_Attachment_Filename = @Dosyaadi,
@query_result_no_padding=1,
@query_result_width = 32767,
@Query_Result_Separator = @Delimiter  ,
@execute_query_database = 'DATABASENAME'

 
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF  
 

//HTML FORMAT (MAIL_PROFILE Unutma)
DECLARE @HTML VARCHAR(MAX);  

SET @HTML = CAST(DAY(GETDATE()) AS VARCHAR)+'.'+CAST(MONTH(GETDATE()) AS VARCHAR)+'.'+CAST(YEAR(GETDATE()) AS VARCHAR)+' '+
				CAST(DATEPART(HOUR, GETDATE()) AS VARCHAR)+':'+CAST(DATEPART(MINUTE, GETDATE()) AS VARCHAR)+' Tarihli Xx Aktarım Kontrolü
<table border="1">
    <tr>
        <th>TABLO</th>
        <th>BUGUN_XXDEN_LOGOYA_AKTARILAN_SATIR_ADET</th> 
    </tr>' +  
    CAST ( 
    (
        SELECT TOP 20 
            td = TABLO, '', 
            td = BUGUN_XXDEN_LOGOYA_AKTARILAN_SATIR_ADET
        FROM [LOGO].[dbo].[BM_301_XXTRANSACTIONSMAIL]
        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) + '
</table>';


-- Envia o e-mail
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'MAIL_PROFILE', -- sysname
    @recipients = 'xx@gmail.com', -- varchar(max)
    @subject = N'Bilmark XxTransaction Control', -- nvarchar(255)
    @body = @HTML, -- nvarchar(max)
    @body_format = 'html'

//Clear mail queue
DECLARE @GETDATE datetime  
SET @GETDATE = GETDATE();  
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE;  
GO