June 16, 2011 at 2:13 am
Hi all,
I need help with the following. The code below works fine but I would like to change the subject of mail based on the current date of the database(server) with that of a date i stored in a table on another.
This is a Log Shipping setup. On the production I get the current date and put this in a table. A few hours later I read the output on the secondary. If the date is the same I’m double sure the Log Ship database is up to date.
What I can’t seem to work out is how to change the subject of the based on the output of the check query. If the dates are the same I would like the subject to include ‘Success’ if they are not the same I would like it to be ‘Failure’. Can anyone help me on my way?
This is the check script I use..
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Header</H1>' +
N'<table border="1">' +
N'<th>Date</th></tr>' +
CAST ( ( SELECT td = CurrentDate
FROM LOG_SHIP_CHECK
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName', @recipients='bla@mail.com',
@subject = 'Check LogShip xxx',
@body = @tableHTML,
@body_format = 'HTML' ;
Thanx, DImitry
June 16, 2011 at 12:53 pm
d.raes
Hi all,
I need help with the following. The code below works fine but I would like to change the subject of mail based on the current date of the database(server) with that of a date i stored in a table on another.
This is a Log Shipping setup. On the production I get the current date and put this in a table. A few hours later I read the output on the secondary. If the date is the same I’m double sure the Log Ship database is up to date.
What I can’t seem to work out is how to change the subject of the based on the output of the check query. If the dates are the same I would like the subject to include ‘Success’ if they are not the same I would like it to be ‘Failure’. Can anyone help me on my way?
This is the check script I use..
DECLARE @tableHTML NVARCHAR(MAX) ;SET @tableHTML =
N'<H1>Header</H1>' +
N'<table border="1">' +
N'<th>Date</th></tr>' +
CAST ( ( SELECT td = CurrentDate
FROM LOG_SHIP_CHECK
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName', @recipients='bla@mail.com',
@subject = 'Check LogShip xxx',
@body = @tableHTML,
@body_format = 'HTML' ;Thanx, DImitry
try this it should give you a place to start
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Header</H1>' +
N'<table border="1">' +
N'<th>Date</th></tr>' +
CAST ( ( SELECT td = CurrentDate
FROM LOG_SHIP_CHECK
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName', @recipients='bla@mail.com',
@subject = 'Check LogShip xxx',
@body = @tableHTML,
@query = 'Select Name from master.dbo.sysdatabases' ,---put what you need here
@attach_query_result_as_file = 1,
@body_format = 'HTML' ;
June 17, 2011 at 7:18 am
This is my take on what you are after:
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @LogShipDate DATETIME2, @productionDate DATETIME2
DECLARE @SuccessFailureText VARCHAR(15)
DECLARE @subjectline VARCHAR(150)
SET @LogShipDate = ( SELECT TOP 1 CurrentDate --Or whatever date you want to return here
FROM LOG_SHIP_CHECK
)
SET @productionDate = (SELECT TOP 1 productiondategoeshere FROM yourProductionTable)
/*
You probably want to cast or convert the two dates for comparison
and then use a case when statement for success/failure and then
change the set @subjectline to include the outcome?
*/
SET @subjectline = N'Check LogShip ' + @LogShipDate + ': ' + @SuccessFailureText
SET @tableHTML = N'<H1>Header</H1>'
+ N'<table border="1">'
+ N'<th>Date</th></tr>'
+ CAST(( SELECT td = CurrentDate
FROM LOG_SHIP_CHECK
FOR
XML PATH('tr')
, TYPE
) AS NVARCHAR(MAX))
+ N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName'
, @recipients = 'bla@mail.com'
, @subject = @subjectline
, @body = @tableHTML
, @body_format = 'HTML' ;
gsc_dba
June 20, 2011 at 1:13 am
Everyone,
Thanks for the reply's here is the end result, maybe someone can use it somehow 🙂
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = getdate();
DECLARE @subjectCheck AS NVARCHAR(MAX);
IF EXISTS (SELECT CurrentDate from LOG_SHIP_CHECK WHERE CurrentDate = @datevar)
SET @subjectCheck = 'Check LogShip: Success'
ELSE
SET @subjectCheck = 'Check LogShip: Failure'
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Ezis Raadpleeg</H1>' +
N'<table border="1">' +
N'<th>Datum</th></tr>' +
CAST ( ( SELECT td = CurrentDate
FROM LOG_SHIP_CHECK
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Myprofile', @recipients='nospam@abc.com',
@subject = @subjectCheck,
@body = @tableHTML,
@body_format = 'HTML' ;
Thanks again for the help!
Grt DImitry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply