SET QUOTED_IDENTIFIER ON

SELECT *
INTO ##TEMP_SSRS_FAILURES
FROM (
	SELECT
		ItemPath,
		RequestType,
		ItemAction,
		CAST(TimeStart AS DATETIME2(0)) AS [TimeStart],
		[Source],
		[Status],
		UserName
	FROM [ReportServer].dbo.ExecutionLog3
	WHERE
		[Status] IN ('rsProcessingError', 'rsProcessingAborted')
		AND
		ItemPath NOT IN ('', 'Unknown')
		AND
		ItemPath NOT LIKE '/Developer%'
		AND
		TimeStart >= DATEADD(hh, -24, GETDATE())
) a

IF (@@ROWCOUNT > 0)
BEGIN
	DECLARE
	@name_str varchar(150) = N'Failed_SSRS_Reports_' + CAST(DATEADD(DD,-0, CAST(GETDATE() AS DATE)) AS VARCHAR(10)),
	@db_name varchar(50) = N'master',
	@query_str varchar(4000) = N'SET NOCOUNT ON; SELECT * FROM ##TEMP_SSRS_FAILURES ORDER BY TimeStart DESC'

	DECLARE
	@subject_str varchar(150) = @name_str,
	@attachment_str varchar(150) = @name_str + '.csv'

	EXEC msdb.dbo.sp_send_dbmail
	--@profile_name = N'Default',
	@recipients = N'me@email.com',
	@reply_to = N'Alerts@email.com',
	@blind_copy_recipients = N'Alerts@email.com',
	@execute_query_database = @db_name,
	@subject = @subject_str,
	@query = @query_str,
	@query_result_header = 1,
	@query_result_separator = '	', --"Tab" recognized by excel
	@query_result_no_padding = 1,
	@exclude_query_output = 1,
	@append_query_error = 1,
	@attach_query_result_as_file = 1,
	@query_result_width = 32767,
	@query_attachment_filename = @attachment_str,
	@body = 'Report Attached'
END