February 27, 2012 at 1:01 pm
Hello,
This is my first post here so please let me know if this question should be in another forum category. I perused them all and this seemed like the best fit. I'm trying to send out an email as a job step that contains an HTML table. I wouldn't mind if the table sends as an attachment or within the body of the email. I have run up against a wall when I try to refere to the variable (@htmlTable) in the @body= parameter of EXEC msdb.dbo.sp_send_dbmail. I am finding references that suggest that this must be a string literal and that variables outside of the sproc cannot be accessed. I cannot pass it in as it isn't one of the allowed parameters. I am a beginner at writing sprocs. Here is my code:
USE [msdb]
GO
/****** Object: Job [Maint: Monitor Scheduled Jobs] Script Date: 02/15/2012 09:51:24 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 02/15/2012 09:51:24 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Maint: Monitor Scheduled Jobs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SchedUser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Get Status of All Scheduled Jobs] Script Date: 02/15/2012 09:51:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get Status of All Scheduled Jobs',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE[TJC_Custom]
-- Variable Declarations
DECLARE @PreviousDate datetime
--DECLARE @PreviousTimeHours datetime
--DECLARE @PreviousTimeMinutes datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @Hour VARCHAR(2)
DECLARE @HourPre VARCHAR(2)
DECLARE @Minute VARCHAR(2)
DECLARE @MinutePre VARCHAR(2)
DECLARE @Second VARCHAR(2)
DECLARE @SecondPre VARCHAR(2)
DECLARE @FinalDate VARCHAR(16)
DECLARE @@tableHTML NVARCHAR(MAX) ;
-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SELECT @HourPre = CONVERT(VARCHAR(2),DATEPART(HOUR, @PreviousDate))
SELECT @Hour = RIGHT(CONVERT(VARCHAR, (@HourPre + 1000000000)),2)
SELECT @MinutePre = CONVERT(VARCHAR(2),DATEPART(MINUTE, @PreviousDate))
SELECT @Minute = RIGHT(CONVERT(VARCHAR, (@MinutePre + 1000000000)),2)
SELECT @Second = DATEPART(SECOND, @PreviousDate)
SET @FinalDate = @Year + @Month + @Day + @Hour +@Minute+@Second
SET @@tableHTML =
N''<H1>Maintenance Monitoring for myServer</H1>'' +
N''<table border="1">'' +
N''<tr><th>Run Date</th><th>Run Time</th>'' +
N''<th>Job Name</th><th>Step</th><th>Step Name</th>'' +
N''<th>Status</th><th>Message</th></tr>'' +
CAST ( ( select td = [Run Date], '''',
td = [Run Time], '''',
td = Convert( varchar(30), [Job Name] ), '''',
td = [Step ID], '''',
td = Convert( varchar(30), [Step Name] ),'''',
td = status, '''',
td = Convert( VarChar(250), Message )
from [TJC_Custom]..[tblDailyJobStatus]
order by [run date], [run time], [step name]
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''</table>'';
PRINT @PreviousDate
PRINT @Year
PRINT @MonthPre
PRINT @Month
PRINT @DayPre
PRINT @Day
PRINT @HourPre
PRINT @Hour
PRINT @Minute
PRINT @Second
PRINT @FinalDate
PRINT @@tableHTML
If Exists
(Select*
Fromdbo.sysobjects
Whereid = object_id(N''[TJC_Custom].[dbo].[tblDailyJobStatus]'')
AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
Drop Table[TJC_Custom].[dbo].[tblDailyJobStatus]
Create Table[TJC_Custom].[dbo].[tblDailyJobStatus]
(
[Run Date] BigInt null,
[Run Time] BigInt null,
[Job Name]SysNamenull,
[Step ID]BigIntnull,
[Step Name]SysNamenull,
[Status]Char(9)null,
[Message]NVarChar(MAX)null)
Insert Into[TJC_Custom].[dbo].[tblDailyJobStatus]
SELECT
h.run_date as ''Run Date'',
h.run_time as ''Run Time'',
j.[name] as ''Job Name'',
h.step_id as ''Step ID'',
h.step_name as ''Step Name'',
[Status] = case (h.run_status)
When 0 Then ''Failed''
When 1 Then ''Succeeded''
Else ''Unknown''
End,
h.message as ''Message''
FROMmsdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps sON j.job_id = s.job_id
AND h.step_id = s.step_id
where cast(convert(int,h.run_date)as varchar(8))+right(REPLICATE(''0'',6)+cast(convert(int,h.run_time) as varchar(6)),6) > @FinalDate
and
j.[name] Not like ''ServerXX%''
AND j.[name] Not Like ''Replication%''
AND j.[name] Not Like ''Distribution%''
AND j.[name] Not Like ''Agent%''
AND j.[name] Not Like ''Expired%''
ORDER BYh.instance_id',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Send Report] Script Date: 02/15/2012 09:51:25 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Report',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC msdb.dbo.sp_send_dbmail
@recipients=N'myemail@yes.no',
@body=N'@tableHTML'
@body_format = ''HTML'',
@subject =''SQL Server Job Status: MyServer'',
@query =''Select [Run Date],
[Run Time],
Convert( varchar(30), [Job Name] ) As [Job Name],
[Step ID] As [Step],
Convert ( VarChar(20),
[Step Name] ) As [Step Name],
Status,
Convert ( VarChar(120), Message ) As Message
From [TJC_Custom]..[tblDailyJobStatus]
order by [run date], [run time], [step name]'',
@attach_query_result_as_file = 1,
@query_attachment_filename =''Results.txt'',
@profile_name = ''SQL Mail''',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily 7:05 AM',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081006,
@active_end_date=99991231,
@active_start_time=000000,
@active_end_time=235959,
@schedule_uid=N'2f2f9d2c-abec-4c2d-96ef-23223d1dee4e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Is there a way that I can pass the variable in or a different way of attacking this?
Tank you for your time and expertise.
February 27, 2012 at 1:13 pm
Wouldn't it be easier to use a stored procedure that would create the html formatted table as a nvarchar(max) variable and call that sproc with @command?
I, personally, wouldn't put that much code in a job step.
February 27, 2012 at 1:34 pm
Thanks Lutz. My first reply ever. How do I do that please?
February 27, 2012 at 1:52 pm
Lutz has suggested a stored procedure but I don't know exactly what he means or what I would be replacing with a sproc. I am too much of a beginner to figure this out so I welcome more information. Thanks!
February 27, 2012 at 1:54 pm
Are you familiar with writing stored procedures?
I would break the sproc into three parts:
1) get the html table and assign it to @tableHTML
2) depending on an input parameter create [TJC_Custom].[dbo].[tblDailyJobStatus] (or do it without a conditional parameter)
3) depending on another input parameter, send the email.
4) Remove the job step 'Send Report'
5) Add the parameter @database_name = 'TJC_Custom' to your sp_add_jobstep command and change the value for @command to:
N'exec dbo.YourNewSproc (@CreateTable =1, @SendMail=1)'
February 27, 2012 at 2:05 pm
Thanks for the detailed and fast reply Lutz! I have just started writing stored procedures. I have programmed some in other languages a bit. I will want to send the email without any conditions regardless of what is or is not in the TJC_Custom.dbo.tblDailyJobStatus (I just want the selected variables of that table). What other input parameter are you referring to in step 3) of your solution?
Would I be throwing out use of the sp_send_dbmail sproc?
Thanks for all you time and help so far. Are you saying I should scrap the way that I am using sp_send_dbmail and the job steps? I am using this as a job that runs on a schedule in sql server so I need to create the table and insert records from sysjobhistory before I create the HTML.
I am not disagreeing with your suggestion at all, it's just that I may not have the vision to determine what I need to cut and insert into my code. Is there a way I can use most of my code or is it just not tenable if I want to send HTML via email?
Thanks again.
P.S. When I used this to send an attachment with ugily formatted query results, it worked fine. It's when I tried to get fancy with the HTML that I hit a wall.
February 27, 2012 at 2:28 pm
Ok, here's a first draft that might help you to get started.
After a closer look to the code you've posted I don't think there's a need to use a parameter for whether or not to re-create [tblDailyJobStatus]. seems like it's needed anyway.
So, here's my first approach (just coded, not tested in any way).
As a side note: The PRINT statement within the code will only maks sens if you call the sproc from Management Studio.
USE[TJC_Custom]
GO
/*
EXEC dbo.YourNewSproc (@SendMail = 0) -- process the data without sending the mail
EXEC dbo.YourNewSproc (@SendMail = 1) -- process the data including sending the mail
*/
CREATE PROCEDURE dbo.YourNewSproc @SendMail TINYINT = 0
AS
SET NOCOUNT ON
-- Variable Declarations
DECLARE @PreviousDate DATETIME
--DECLARE @PreviousTimeHours datetime
--DECLARE @PreviousTimeMinutes datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @Hour VARCHAR(2)
DECLARE @HourPre VARCHAR(2)
DECLARE @Minute VARCHAR(2)
DECLARE @MinutePre VARCHAR(2)
DECLARE @Second VARCHAR(2)
DECLARE @SecondPre VARCHAR(2)
DECLARE @FinalDate VARCHAR(16)
DECLARE @tableHTML NVARCHAR(MAX) ; -- no longer needed to be a global variable LM: changed from @@tableHTML to @tableHTML
-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day LM: this means the last 24hrs. Is this intended?
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT
@MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT
@Month = RIGHT(CONVERT(VARCHAR, ( @MonthPre + 1000000000 )), 2)
SELECT
@DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT
@Day = RIGHT(CONVERT(VARCHAR, ( @DayPre + 1000000000 )), 2)
SELECT
@HourPre = CONVERT(VARCHAR(2), DATEPART(HOUR, @PreviousDate))
SELECT
@Hour = RIGHT(CONVERT(VARCHAR, ( @HourPre + 1000000000 )), 2)
SELECT
@MinutePre = CONVERT(VARCHAR(2), DATEPART(MINUTE, @PreviousDate))
SELECT
@Minute = RIGHT(CONVERT(VARCHAR, ( @MinutePre + 1000000000 )), 2)
SELECT
@Second = DATEPART(SECOND, @PreviousDate)
SET @FinalDate = @Year + @Month + @Day + @Hour + @Minute + @Second
-- using the local variable
SET @tableHTML = N'<H1>Maintenance Monitoring for myServer</H1>'
+ N'<table border="1">' + N'<tr><th>Run Date</th><th>Run Time</th>'
+ N'<th>Job Name</th><th>Step</th><th>Step Name</th>'
+ N'<th>Status</th><th>Message</th></tr>'
+ CAST(( SELECT
td = [Run Date],
',td = [Run Time], ',
td = CONVERT(VARCHAR(30), [Job Name]),
',td = [Step ID], ',
td = CONVERT(VARCHAR(30), [Step Name]),
',td = status, ',
td = CONVERT(VARCHAR(250), Message)
FROM
[TJC_Custom]..[tblDailyJobStatus]
ORDER BY
[run date],
[run time],
[step name]
FOR
XML PATH('tr'),
TYPE ) AS NVARCHAR(MAX)) + N'</table>' ;
PRINT @PreviousDate
PRINT @Year
PRINT @MonthPre
PRINT @Month
PRINT @DayPre
PRINT @Day
PRINT @HourPre
PRINT @Hour
PRINT @Minute
PRINT @Second
PRINT @FinalDate
PRINT @tableHTML
IF EXISTS ( SELECT
*
FROM
dbo.sysobjects
WHERE
id = OBJECT_ID(N'[TJC_Custom].[dbo].[tblDailyJobStatus]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
DROP TABLE[TJC_Custom].[dbo].[tblDailyJobStatus]
CREATE TABLE [TJC_Custom].[dbo].[tblDailyJobStatus]
(
[Run Date] BIGINT NULL,
[Run Time] BIGINT NULL,
[Job Name] SYSNAME NULL,
[Step ID] BIGINT NULL,
[Step Name] SYSNAME NULL,
[Status] CHAR(9) NULL,
[Message] NVARCHAR(MAX) NULL
)
INSERT INTO [TJC_Custom].[dbo].[tblDailyJobStatus]
SELECT
h.run_date AS 'Run Date',
h.run_time AS 'Run Time',
j.[name] AS 'Job Name',
h.step_id AS 'Step ID',
h.step_name AS 'Step Name',
[Status] = CASE ( h.run_status )
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
ELSE 'Unknown'
END,
h.message AS 'Message'
FROM
msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON
h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON
j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE
CAST(CONVERT(INT, h.run_date) AS VARCHAR(8))
+ RIGHT(REPLICATE('0',6)+ CAST(CONVERT(INT, h.run_time) AS VARCHAR(6)),6)
> @FinalDate
AND j.[name] NOT LIKE 'ServerXX%'
AND j.[name] NOT LIKE 'Replication%'
AND j.[name] NOT LIKE 'Distribution%'
AND j.[name] NOT LIKE 'Agent%'
AND j.[name] NOT LIKE 'Expired%'
ORDER BY
h.instance_id
IF @SendMail = 1
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients = N'myemail@yes.no',
@body = @tableHTML, @body_format = 'HTML',
@subject = 'SQL Server Job Status: MyServer',
@query = 'Select [Run Date],
[Run Time],
Convert( varchar(30), [Job Name] ) As [Job Name],
[Step ID] As [Step],
Convert ( VarChar(20),
[Step Name] ) As [Step Name],
Status,
Convert ( VarChar(120), Message ) As Message
From [TJC_Custom]..[tblDailyJobStatus]
order by [run date], [run time], [step name]',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Results.txt', @profile_name = 'SQL Mail',
@database_name = N'master', @flags = 0
END
February 27, 2012 at 2:34 pm
Thanks Lutz! I will get going on getting it to work with your very helpful input. Yes, you caught me.. I had the PRINT statements commented out this weekend after I checked the variable contents and somehow I uncommented them during my work today to get the HTML formatting to work. Good catch. For anyone reading this later: the print statements are a side issue that's not related to the solution. When I get this working, I will post with the working code.
Again, thanks for hanging in there with me Lutz!
P.S. Yes, the last 24 hours are what I wanted, not just today's date's worth.
February 27, 2012 at 2:48 pm
Hi again Lutz,
When I ran the sproc I got this message:
Msg 8145, Level 16, State 1, Procedure sp_send_dbmail, Line 0
@database_name is not a parameter for procedure sp_send_dbmail.
It doesn't like that I'm passing in a "foreign" parameter that's not builtin?
When I see the code in the query window in SSMS there is the red squiggly line under @database_name = N'master', @flags = 0 at the very bottom before END but it did compile. I ran it with "1" as the parameter and see the error msg 8145 above.
Thanks.
February 27, 2012 at 2:54 pm
Please remove the last line
@database_name = N'master', @flags = 0
and the preceeding comma.
I accidentally copied the last two parameter of the sp_add_jobstep parameter. I'm sorry.
February 27, 2012 at 3:01 pm
Thank you. Yes, I actually tried that and I got an email. Now I am just trying to get it formatted because it looks like this:
,td=[Run Time],
,td=[Step ID],
,.
.
.
.
then a nicely formatted table with the step name, status, and message columns (the last 3 columns) with headers only and no data. Nice progress though.
I will post again when I figure this out.
Thanks Lutz!
February 27, 2012 at 3:09 pm
Here's what I changed to get the formatting on the table just right:
+ CAST(( SELECT
td = [Run Date],
'',
td = [Run Time],
'',
td = CONVERT(VARCHAR(30), [Job Name]),
'',
td = [Step ID],
'',
td = CONVERT(VARCHAR(30), [Step Name]),
'',
td = status,
'',
td = CONVERT(VARCHAR(250), Message)
FROM
[TJC_Custom]..[tblDailyJobStatus]
I added the '', spacers between the table cells (td tags).
February 27, 2012 at 5:04 pm
I've got it all working now but I want to make sure I have everything 100% before I post the final code. I removed all the kludge that referred to email in the original code I submitted and add a call to the stored procedure that Lutz helped me so much with, as another job step. I'll post soon with the whole code listing and description in case anyone likes this as a way to email polished looking system/database monitoring via the SQL Server Agent job system.
Thanks Lutz 😀
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply