November 5, 2013 at 8:55 am
This sample code should work if folks want to test this - just add your email in the 2 sections [insert email]. As the code points out, I can get the one version to work,
But cannot get the second version to work, it will not execute the SQL. I used this as examples.
DECLARE @HTML NVARCHAR(MAX) ;
DECLARE @SQL NVARCHAR(MAX) ;
-- Start Trap Error testing
BEGIN TRY
SELECT convert(smallint, '2003121') AS failcase
END TRY
BEGIN CATCH
SELECT
ltrim(str(error_number())) AS ErrorNumber
,ltrim(str(error_severity())) AS ErrorSeverity
,ltrim(str(error_state())) AS ErrorState
,ltrim(str(error_procedure())) AS ErrorProcedure
,ltrim(str(error_line())) AS ErrorLine
,ltrim(ERROR_MESSAGE()) AS ErrorMessage
INTO ##tmpError_tbl
-- part of testing not part of functional code, yet
Set @SQL = 'Select * FROM ##tmpError_tbl'
/* Code works, but output is sucky
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'clb39@cornell.edu',
@subject = ' DB Mail TSQL test ',
@query_result_no_padding = 1,
@body_format= 'html',
@query = 'SELECT * FROM ##tmpError_tbl';
*/
/* Section does not currently work. causes error: Msg 156, Level 15, State 1, Line 30 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 30 Incorrect syntax near '+'.*/
SET @HTML =
N'<style type="text/css"> .problem {} .blocked {}</style>' +
N'<div><span style="background-color:red; color:white; font-style:!important; padding:10px; margin-bottom:15px; -webkit-border-radius: 10px; border-radius: 10px; width:auto;display: inline-block;">This is the section for the problem query
' +
@SQL + --Works but does not execute the query
-- EXEC sp_executesql(SELECT * FROM ##tmpError_tbl) + -- Error near exec and +
-- (Exec(@SQL)) + -- does not work, still does not like the formatting
-- Exec(Cast ((SELECT * FROM ##tmpError_tbl) AS NVARCHAR(MAX))) + -- Error near Exec and AS
-- Cast ((SELECT * FROM ##tmpError_tbl) AS NVARCHAR(MAX)) + -- Initial failure. Odd b/c there is no Subquery to error on and this approach matches the examples
N'</span></div>'
--@query = 'SELECT * FROM ##tmpError_tbl',
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'clb39@cornell.edu',
@subject = ' DB Mail TSQL test ',
@query_result_no_padding = 1,
@body = @HTML,
@body_format= 'html';
DROP TABLE ##tmpError_tbl --Needs to be global to work everywhere, local fails in some cases
END CATCH
END CATCH
November 5, 2013 at 1:05 pm
If this is an error notification, you probably don't need anything particularly fancy for the html formatting.
I suggest ditching the temp table and the query in the sp_send_dbmail call, and setting your @HTML variable to use for the email body like this:
SET @HTML =
'<H1>ERROR - DB Mail TSQL test</H1>' +
'<TABLE BORDER="1">' +
'<TR>' +
'<TH>ErrorNumber</TH>' +
'<TH>ErrorSeverity</TH>' +
'<TH>ErrorState</TH>' +
'<TH>ErrorProcedure</TH>' +
'<TH>ErrorLine</TH>' +
'<TH>ErrorMessage</TH>' +
'</TR>' +
CAST
(
(
select
TD = ltrim(str(error_number())), '',
TD = ltrim(str(error_severity())), '',
TD = ltrim(str(error_state())), '',
TD = ltrim(str(error_procedure())), '',
TD = ltrim(str(error_line())), '',
TD = ltrim(ERROR_MESSAGE()), ''
FOR XML PATH('TR'), TYPE)
AS NVarChar(Max)
) +
'</TABLE>';
November 5, 2013 at 6:59 pm
sestell1, thank you for showing the errors in my code. Fantastic. I've been able to tweak the code then put it into a stored procedure with params for subject and email so our devs can easily trap/report errors.
November 6, 2013 at 7:21 am
No problem, glad I could help. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply