Need help on HTML formatted email from Stored Procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Need help on HTML formatted email from Stored...

Need help on HTML formatted email from Stored Procedure

Rate Topic

Display Mode

Topic Options

Author

Message

jaype-365633

Posted Wednesday, April 28, 2010 12:27 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:37 PM
Points: 17, Visits: 107

Hi, i had created a trigger to send email whenever the new data is inserted or updated which sends the email in one line, i need to send the email in HTML format from stored Procedure, please help.
Attached the image how the mail to be sent.

Post Attachments

SP.JPG (52 views, 21.98 KB)

Post #911637

LutzM

Posted Wednesday, April 28, 2010 3:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

Please provide table def and sample data as described in the first link in my signature.
It will help us to test our solutions.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #911736

jaype-365633

Posted Wednesday, April 28, 2010 4:46 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 11:37 PM
Points: 17, Visits: 107

Hi Lutz, i am not getting the place to post on your first link, in trigger i have used
/****** Object: Trigger [dbo].[New_Author] Script Date: 04/28/2010 16:01:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[New_Author] ON [dbo].[CLINICIANS] AFTER INSERT, UPDATE AS
DECLARE @SOURCEID VARCHAR(50)
DECLARE @DATAID varchar(50)
DECLARE @DEPTID varchar(50)
DECLARE @AID varchar(50)
DECLARE @ARDESC varchar(50)
DECLARE @HID varchar(50)
SET @SOURCEID = (select sourceid from inserted)
SET @DATAID = (SELECT TRUSTID FROM inserted)
SET @AID = (SELECT AUTHORID FROM inserted)
SET @DEPTID = (SELECT DEPTID FROM inserted)
SET @AD = (SELECT AUTHORDESC FROM inserted)
SET @HID = (SELECT HOSPITALID FROM inserted)
IF (UPDATE (AUTHORID))
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'NEW EMP "' + @aid + '" RECEIVED IN THE "' +@SOURCEID+'.' + @DATAID + '" AND DESCRIPTION IS "'
+ @ARDESC + '" AND THE DEPARTMENT IS "' + @DEPTID + '"'
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'jaiprakash23@hotmail.com',
@copy_recipients='jaiprakash23@hotmail.com',
@body= @MSG,
@subject = 'New EMP Received',
@profile_name = 'Jaype'
END

getting the mail in one line as
NEW EMP "2008" RECEIVED IN THE "IT" AND THE DESCRIPTION IS "Information Technolog" AND THE DEPARTMENT IS "Sysadmin"

Post #911770

shen-dest

Posted Wednesday, April 28, 2010 4:56 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:23 AM
Points: 244, Visits: 409

Hi,
you can use HTML tags to format your @MSG variable.We send html-formatted mails for other purposes.

Regards,
MShenel

Post #911775

LutzM

Posted Wednesday, April 28, 2010 12:17 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

Your trigger code won't work as soon as there is more than 1 row affected.
You need to rethink your process to decide how to deal with that.

Post #912271

« Prev Topic | Next Topic »

Permissions