Post
Ask Question
zain abbas

zain abbas

  • NA
  • 81
  • 20k

How to send email from sqlserver ?

Mar 4 2014 1:02 AM
i tried this .
 
CREATE PROCEDURE uspClientCancellationData
AS
BEGIN
declare @recipient varchar(500)
declare @body varchar(max)
declare @htmlBody = VARCHAR(MAX)
set @recipient ='[email protected]'
SET @htmlBody =
N'<table>' +
N'<tr>
<th>CustomerNo</th>
<th>ClientName</th>
<th>Joined</th>
<th>ExpiryDate</th>
<th>Cluster</th>
<th>cppcp</th>
<th>CacellationDate</th>
<th>CacellationReson</th>
<th>Package</th>
<th>Region</th>
<th>EnteredBy</th>
</tr>' +
CAST ((
select
td = CAST([t1.Client_CustomerNumber] AS VARCHAR(100)), ''
,td=t1.Client_Name ,'',
td=CAST ([t1.Client_CreatedDate] AS VARCHAR(100)),''
,td=CAST([t1.Client_ExpiryDate] AS VARCHAR(100)),'',
td=t2.Cluster_Name,'',
td=t3.ClientType_Name,'',
td=CAST ([t4.Cancellation_createddate] AS VARCHAR(100),'',
td=t5.Reason_Text,'',
td=t6.Package_Name,'',
td=t7.Region_Name ,'',
td=t8.FirstName
from Clients t1 left join Clusters t2 on t1.Client_ID=t2.Cluster_ID
left join ClientTypes t3 on t1.Client_ID=t3.ClientType_ID
left join Cancellations t4 on t1.Client_ID=t4.Cancellation_id
left join CancellationReasons t5 on t1.Client_ID=t5.Reason_id
left join Packages t6 on t1.Client_ID=t6.Package_ID
left join Regions t7 on t1.Client_ID=t7.Region_Id
left join aspnet_Membership t8 on t4.Cancellation_ID_CreatedByUser = t8.[User_ID]
where t4.Cancellation_createddate between dateadd(day, -7, getdate()) and getdate()
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) +
N'</table>'
SET @body ='<html><H1>Cancellation Email</H1><body bgcolor=yellow><table border = 2><tr></tr>' SET @body = @htmlBody +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@body = @body,@body_format ='HTML',
@subject ='Client Cancellation Record Data ',
@profile_name ='DatabaseMailProfile'
END
GO
 

Answers (2)