Create a stored procedure that converts query output to HTML table
USE [databasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spQueryToHtmlTable]
(
@query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
@orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
@html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN
DECLARE @borderColor char(7) = '#cccccc'
SET NOCOUNT ON;
IF @orderBy IS NULL BEGIN
SET @orderBy = ''
END
SET @orderBy = REPLACE(@orderBy, '''', '''''');
DECLARE @realQuery nvarchar(MAX) = '
DECLARE @headerRow nvarchar(MAX);
DECLARE @cols nvarchar(MAX);
SELECT * INTO #dynSql FROM (' + @query + ') sub;
SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;
SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''
EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT
SELECT @headerRow = COALESCE(@headerRow + '''', '''') + '''' + name + '' | ''
FROM tempdb.sys.columns
WHERE object_id = object_id(''tempdb..#dynSql'')
ORDER BY column_id;
SET @headerRow = '''' + @headerRow + ''
'';
SET @html = ''
'' + @headerRow + @html + ''
'';
';
EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO
Create a table that holds email recipients
USE [databasename]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[errorsRecipients](
[id] [int] IDENTITY(1,1) NOT NULL,
[recipient] [varchar](512) NOT NULL,
[enabled] [bit] NULL,
CONSTRAINT [PK_errorsRecipients] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[errorsRecipients] ADD DEFAULT ((1)) FOR [enabled]
GO
Insert some data in the recipients table
USE [databasename]
GO
INSERT INTO [dbo].[errorsRecipients]
([recipient]
,[enabled])
VALUES
('some.emial.address@some.domain',
1)
GO
Execute email send
DECLARE @html NVARCHAR(MAX)
DECLARE @emailRecipients NVARCHAR(MAX)
SELECT @emailRecipients = COALESCE(@emailRecipients + '; ', '') + recipient
FROM errorsRecipients
WHERE enabled = 1
EXEC [dbo].[spQueryToHtmlTable]
@query = 'SELECT QUERY HERE',
@html = @html OUTPUT
EXEC msdb.dbo.sp_send_dbmail
@recipients=@emailRecipients,
@subject='The subject',
@body=@html,
@body_format='HTML',
@from_address='SENDER NAME <youraddress@yourdomain.com>',
@reply_to='noreply@somedomain.com';