July 24, 2015 at 5:35 am
EXEC msdb.dbo.sp_send_dbmail
@subject = 'Job Summary',
@profile_name = 'SQL SMTP',
@recipients = 'email address',
@body = @body,
@body_format ='HTML',
@query =
'
DECLARE @body NVARCHAR(MAX)
SET @body = N '<table>'
+ N'<tr>
<th>Dbname</th>
<th>Transactiondate</th>
</tr>'
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t1
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t2
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t3
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
Thank you in advance!
July 24, 2015 at 5:45 am
tt-615680 (7/24/2015)
EXEC msdb.dbo.sp_send_dbmail@subject = 'Job Summary',
@profile_name = 'SQL SMTP',
@recipients = 'email address',
@body = @body,
@body_format ='HTML',
@query =
'
DECLARE @body NVARCHAR(MAX)
SET @body = N '<table>'
+ N'<tr>
<th>Dbname</th>
<th>Transactiondate</th>
</tr>'
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t1
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t2
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t3
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
Thank you in advance!
What are you thanking us for? You haven't asked for anything.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 24, 2015 at 7:11 am
Dear All,
I apologise for the previous post!
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.
Here is what I've done so far but I'm having trouble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject = 'Job Summary',
@profile_name = 'SQL SMTP',
@recipients = 'email address',
@body = @body,
@body_format ='HTML',
@query =
'
DECLARE @body NVARCHAR(MAX)
SET @body = N '<table>'
+ N'<tr>
<th>Dbname</th>
<th>Transactiondate</th>
</tr>'
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t1
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t2
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
+ CAST((
SELECT TOP 1 CONVERT(date, Date)
FROM t3
WHERE Date <= getdate()
ORDER BY Date DESC
FOR XML RAW('tr'), ELEMENTS
)
AS NVARCHAR(MAX))
Thank you!
July 24, 2015 at 9:07 am
quite a few issues there.
when you call sp_send_dbmail, you cannot append stuff togetierh for the paramters, you have to do that in advance, it has to be a single assignment, i thinkso it's a single static string, or a variable.if you need to put things together, it has to be done prior to teh proc call.
ie like this:
DECLARE @sqlcmd nvarchar(max) = 'SELECT ' + db_name() + ' As DB, Big Long Query featuring appending items together...'
DECLARE @EmailList varchar(max) = 'lowell@somedomain.com' + ';' + 'tt-615680@anotherDomain.com'
EXEC msdb.dbo.sp_send_dbmail
@subject = 'Job Summary',
@profile_name = 'SQL SMTP',
@recipients = @EmailList,
@body = @body,
@body_format ='HTML',
@query = @sqlcmd
the seocnd piece is that you probably need to use something like this for part of your @bodyv variable, this is just a guess, but it's probably close:
declare @body varchar(max)
SET @body = '<table>
<tr>
<th>'
+ Db_name()
+'</th>
<th>' + CONVERT(VARCHAR,getdate(),111)
+ '</th>
</tr>'
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply