May 12, 2015 at 3:37 pm
hello
I have setup Database mail on my SQL Server 2012.
I have been successful in generating daily reports and then compiling all the reports into a zip file on a daily schedule.
I have tested a procedure to automatically attach the file to a email message and mail it out.
However the zip file is not created everyday. (the file is only created when we are batching product)
I am looking for a way to check the file exists before I attach it to the email message.
Thank you
DECLARE @isExists INT
exec master.dbo.xp_fileexist 'E:\ZipBatches\ReactorBatch.zip',
@isExists OUTPUT
SELECT case @isExists
when 1 then 'yes'
else 'No'
end as isExists
USE msdb
EXEC sp_send_dbmail
@profile_name='CFServerMail',
@recipients='georgeg@communityfuels.com',
@subject='Test Attachment',
@body='An attachement has been included in this email.',
@file_attachments='E:\ZipBatches\ReactorBatches.zip'
May 12, 2015 at 5:37 pm
Is this what you're up to?
DECLARE @isExists INT
EXEC master.dbo.xp_fileexist 'E:\ZipBatches\ReactorBatch.zip', @isExists OUTPUT
IF @isExists = 1
BEGIN
USE msdb
EXEC sp_send_dbmail
@profile_name='CFServerMail',
@recipients='georgeg@communityfuels.com',
@subject='Test Attachment',
@body='An attachement has been included in this email.',
@file_attachments='E:\ZipBatches\ReactorBatches.zip'
END
ELSE
BEGIN
SELECT 'What do you want to have in case there is no file?'
END;
May 13, 2015 at 1:03 pm
Thanks
I found some code that I modified and it is working well
DECLARE @isExists INT
exec master.dbo.xp_fileexist 'E:\ZipBatches\ReactorBatches.zip',
@isExists OUTPUT
SELECT case @isExists
when 1 then 'yes'
else 'No'
end as isExists
IF @isExists = 1
BEGIN
USE msdb
EXEC sp_send_dbmail
@profile_name='CFServerMail',
@recipients='georgeg@communityfuels.com',
@subject='Batch Reports for Yesterday',
@body='This is an auto generated mail message, Please DO NOT Reply
Batch Reports are included in this email as a Zip File.',
@file_attachments='E:\ZipBatches\ReactorBatches.zip'
END
IF @isExists = 0
BEGIN
USE msdb
EXEC sp_send_dbmail
@profile_name='CFServerMail',
@recipients='georgeg@communityfuels.com',
@subject='NO Batch Reports yesterday',
@body='This is an auto generated mail message, Please DO NOT Reply
NO Batch Reports are available for yesterday.'
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply