July 28, 2011 at 2:20 am
Hi
I have a task which should trigger an email of the product scanning the a table where amount greater than 40000.
For this I have written the below Linked server Stored procedure and it works great.
Now I want to include similar kind of another 2,3 alerts on the same email .
ie i need to add another alert on the existing scripts so that there would be 2,3 html tables will be in one email.
I dont know how to club all the multiple record sets on the body of the email.
can you please help me out.
Thanks a lot in advance.
regards
Shan
USE [team]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Testalert]
As
Begin
Declare @x as bigint
SELECT @x =COUNT(*) FROM OPENQUERY([1.1.1.1], 'select productno
from DBname.dbo.TBname (nolock)
where datetime >= DATEADD(HOUR, -1, GETDATE())
and amount > ''40000''')
if (@x > '0')
begin
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @refid VARCHAR(500)
SET @refid = 'Product Purchased > $40000 - Ticket : ' + replace(convert(varchar, getdate(),111),'/','') + replace(convert(varchar, getdate(),108),':','')
SET @tableHTML = N'<html><body><h4>Product Purchaced > $40000 Details</h4>' +
N'<table border="1" width="100%">' +
N'<tr bgcolor="AntiqueWhite"><td><b>From</b></td><td><b>Productno</b></td><td><b>ProductName</b></td><td><b>Amount</b></td><td><b>No of Products</b></td></tr>' +
CAST(
(
select td=td1,'',td=td2,'',td=td3,'',td=td4,'',td=td5,'',td = td6, '' from
( SELECT * FROM OPENQUERY([1.1.1.1], 'select td1 = y.location ,td2 = x.productno , td3 = z.productname, td4 = cast(sum(x.amount) as bigint), td5 = count(*)
from DBname.dbo.ac_audit (nolock)
where datetime >= DATEADD(HOUR, -2, GETDATE())
and amount > ''40000''
')
) t
FOR XML PATH('tr'), TYPE)
AS NVARCHAR(MAX)) +
N'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients='sam@xyz.com',
@subject = @refid,
@body = @tableHTML,
@body_format = 'HTML',
@profile_name='team';
end
End
July 28, 2011 at 7:40 am
Can you just end your first string before </body> and duplicate your logic for tables 2 and 3 and just append your variables?
By the way, you have two instances of comparing a string to an int. In this case it is probably going to work on the implicit conversion.
I assume that amount is not a character datatype.
and amount > ''40000''')
@x holds the value from count(*)
if (@x > '0')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply