Cacher is the code snippet organizer for pro developers

--CHECK OPERA-CRM sync
IF ((SELECT COUNT(*) FROM [someserver].[db].[adacta].[v2_rezervacije_success] WITH (NOLOCK) WHERE CAST(timestamp as date) = CAST(GETDATE() as date)) = 0)
OR ((SELECT COUNT(*) FROM [someserver].[db].[adacta].[v2_profiles_success] WITH (NOLOCK) WHERE CAST(timestamp as date) = CAST(GETDATE() as date)) = 0)
begin

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Adacta Integration Notifications',
@recipients = 'support@company.si', 
@subject = 'Valamar: OPERA-CRM sync: No reservations or profiles have been transfered to CRM',
@query = 'SELECT ''rezervacije'' as entity, COUNT(*) as number_of_errors_today FROM [someserver].[db].[adacta].[v2_rezervacije_error] WITH (NOLOCK) WHERE CAST(timestamp as date) = CAST(GETDATE() as date)
UNION
SELECT ''profile'' as entity, COUNT(*) as number_of_errors_today FROM [someserver].[db].[adacta].[v2_profiles_error] WITH (NOLOCK) WHERE CAST(timestamp as date) = CAST(GETDATE() as date)',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'rezervacije_and_profiles_error.csv'
End

--CHECK CRM-CIS sync
IF ((SELECT COUNT(*) FROM [db]..ACCOUNT WITH (NOLOCK) WHERE CAST(row_modifiedon as date) = CAST(GETDATE() as date)) = 0 OR
(SELECT COUNT(*) FROM [db]..RESERVATION WITH (NOLOCK) WHERE CAST(row_modifiedon as date) = CAST(GETDATE() as date)) = 0 OR
(SELECT COUNT(*) FROM [db]..LOYALTYPROFILE WITH (NOLOCK) WHERE CAST(row_modifiedon as date) = CAST(GETDATE() as date)) = 0)
begin

exec msdb.dbo.sp_send_dbmail
@profile_name = 'Adacta Integration Notifications',
@recipients = 'support@company.si', 
@subject = 'Valamar: CRM_CIS sync: No accounts, reservations or loyaltyprofiles have been transfered to CIS',
@query = 'SELECT ''ACCOUNT'' as entity, COUNT(*) as number_or_records FROM [db]..ACCOUNT WITH (NOLOCK) WHERE CAST(row_modifiedon as date) = CAST(GETDATE() as date)
UNION
SELECT ''RESERVATION'' as entity, COUNT(*)  as number_or_records FROM [db]..RESERVATION WITH (NOLOCK) WHERE CAST(row_modifiedon as date) = CAST(GETDATE() as date)
UNION
SELECT ''LOYALTYPROFILE'' as entity, COUNT(*)  as number_or_records FROM [db]..LOYALTYPROFILE WITH (NOLOCK) WHERE CAST(row_modifiedon as date) = CAST(GETDATE() as date)',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'cis_counts.csv'
End