create procedure [dbo].[sp_send_AuditHistoryHasValuesEmail_email]
as
Begin
declare @recordCount int
select @recordCount = isnull(count(*), 0)
from audithistory WHERE DATEDIFF(d, DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (audittime/1000),'1970-01-01 00:00:00'),GETDATE())>30;
IF (@recordCount > 0)
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'CHMAILSERVER',
@recipients = 'richlemmermann@crouse.org',
@query = 'select @recordCount = isnull(count(*), 0)
from audithistory WHERE DATEDIFF(d, DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (audittime/1000),
''1970-01-01 00:00:00''),GETDATE())>30;' ,
@subject = 'AuditHistory (produced results) Email ',
@Body = 'See if this is causing an issue..... ' ,
@attach_query_result_as_file = 1 ;
End
else
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'CHMAILSERVER',
@recipients = 'richlemmermann@crouse.org',
@BODY = 'No data returned ',
@subject = 'AuditHistory Email'
End
End;
Quick Send Email
declare @tab char(1) = CHAR(9)
exec msdb.dbo.sp_send_dbmail @profile_name='CROUSE', @recipients='richlemmermann@crouse.org', @subject='test', @attach_query_result_as_file=1,
@query = 'exec crousescripts.dbo.sp_LookUpBadge', @query_attachment_filename='filename.csv',@query_result_separator=@tab,@query_result_no_padding=1
--exec crousescripts.dbo.sp_LookUpBadge