Reputation: 149
I have created SSIS package which has 1 SQL task (stored procedure which returns list of below 4 columns. How will achieve the following table to be sent through email. Immediate Next task here what I would do is create another stored procdure which accepts the result set from 1st stored proc through another Sql task and 2nd stored proc contains msdb_SendDBemail call . Is there any better approach
Upvotes: 4
Views: 5708
Reputation: 5643
You can try something like this to get data in tabular format. To execute row by row you need to implement a cursor like this.
CREATE PROCEDURE [dbo].[SendNewDeviceInfoToEmployee]
AS
BEGIN
SET NOCOUNT ON
DECLARE @Mbody VARCHAR(MAX),
@Sub VARCHAR(500),
@EmpEmailPwdId BIGINT,
@Emp_nm VARCHAR(150),
@ToEmailid VARCHAR(150),
@NewEmailId VARCHAR(150),
DECLARE @Recipients VARCHAR(max)
DECLARE SendautoLoginDtl CURSOR FOR
SELECT EmpEmailPwdId, EmpName, ToEmailid, NewEmailId, EmpPassword
FROM EmployeeEmailIdPassword WHERE ISNULL(Freeze,'N') = 'N' AND ISNULL(IsMailSent,'N') = 'N' AND ISNULL(ToEmailId,'') <> '' AND ISNULL(NewEmailId,'') <> ''
ORDER BY EmpEmailPwdId
OPEN SendautoLoginDtl;
FETCH NEXT FROM SendautoLoginDtl INTO @EmpEmailPwdId, @emp_nm, @ToEmailid, @NewEmailId
WHILE( @@FETCH_STATUS = 0 )
BEGIN
SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','', [Ranking Points] AS 'td','', Country AS 'td'
FROM #Temp
ORDER BY Rank
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @Mbody =N'<html><body><H3>Tennis Rankings Info</H3>
<table border = 1>
<tr>
<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'
SET @body = @body + @xml +'</table></body></html>'
SET @sub='Your New Device Details - ' + @emp_nm
set @Recipients = @ToEmailid
EXEC msdb.dbo.sp_send_dbmail '<YourDBMailProfileName>' ,
@recipients = @Recipients ,
@copy_recipients = @NewEmailId,
@subject = @sub ,
@body = @Mbody ,
@body_format = 'HTML'
--Update table if required
UPDATE <YourTable>
SET IsMailSent = 'Y'
WHERE EmpEmailPwdId = @EmpEmailPwdId
FETCH NEXT FROM SendautoLoginDtl INTO @EmpEmailPwdId, @emp_nm, @ToEmailid, @NewEmailId, @EmpPassword
END
CLOSE SendautoLoginDtl;
DEALLOCATE SendautoLoginDtl;
END
You can learn about cursor here.
Upvotes: 2
Reputation: 5940
Nice thread on stack exchange: Need to Send a formatted HTML Email via Database Mail in Sql Server 2008 R2
It has an answer with a code of stored procedure dbo.HtmlTable
that can generate HTML for further use in database mail:
CREATE table ##foo (bar1 int, bar2 varchar(20), bar3 datetime)
INSERT into ##foo values (1, 'Abcdef', getdate())
INSERT into ##foo values (2, 'Ghijkl', '05/05/15')
DECLARE @tableHtml varchar(max)
EXEC dbo.HtmlTable
'##foo',
@tableHtml output
PRINT @tableHtml
@tableHtml will contain html that renders into:
Upvotes: 2