How to send email from sqlserver ?

Question

i tried this .   CREATE PROCEDURE uspClientCancellationData AS BEGIN declare @recipient varchar(500) declare @body varchar(max) declare @htmlBody = VARCHAR(MAX) set @recipient ='zain.abbas@moftak.com' SET @htmlBody = N' ' + N' CustomerNo ClientName Joined ExpiryDate Cluster cppcp CacellationDate CacellationReson Package Region EnteredBy ' + 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' ' SET @body =' Cancellation Email ' SET @body = @htmlBody +' ' EXEC msdb.dbo.sp_send_dbmail @body = @body,@body_format ='HTML', @subject ='Client Cancellation Record Data ', @profile_name ='DatabaseMailProfile' END GO

Lakshmanan Sethu Sankaranarayan · Answer

Are you getting any error message?

Lakshmanan Sethu Sankaranarayan · Answer

Hi
Please refer this link http://www.emailarchitect.net/easendmail/kb/sql.aspx