IF OBJECT_ID('AgeNotfi') IS NOT NULL
exec ('DROP TABLE AgeNotfi')
select code, name,
convert(varchar(20), birth_date,101) as birth_date
into AgeNotfi from View_Elapco_prob_end_date
where birth_date=convert(date,convert (Varchar(12), GETDATE(),111),111)
insert into AgeNotfi
Select 'Code' as Code, 'Name' [Name],
'Birth Date' as [Birth Date]
select * from AgeNotfi
DECLARE @EXEQRY VARCHAR(8000)
DECLARE @SQLSQRY VARCHAR(200)
DECLARE @FILENAME VARCHAR(250)
SET @FILENAME='C:\BCP\AgeAbove64_'+ CONVERT(VARCHAR(12),GETDATE(),105)+ '.csv'
set @SQLSQRY = ' select * from [HR].[DBO].AgeNotfi'
SELECT @EXEQRY='bcp "'+ @SQLSQRY +'" queryout '+ @FILENAME +' -c -t, -Usa -Pwork@elapco -S'+ @@servername
exec master.[dbo].xp_cmdshell @EXEQRY
DECLARE @Message VARCHAR(2000), @Subject VARCHAR(500)
SET @Message = ' Dear Sir/Madam
Please find the attached File.This is an auto generated email.
'
SET @Subject = ' Age Above 65 Notification'
exec msdb..sp_send_dbmail
@recipients='sri@gmail.com',
@subject = @subject,@body = @message,@File_Attachments = @FileName,
@body_format='HTML'
கருத்துகள் இல்லை:
கருத்துரையிடுக